Automation Error Copy Pasting from two worksbooks into third one

[Dokat]

Hi,

I have below code where i copy paste from two workbooks into third one. First oart of the code works fine however after adding the second part it returns run time error -2147221080. I believe its because of opening second workbook but not sure. Anyone has experience with this priblem and have a solution?

Private Sub WorkbookOpen()

Application.ScreenUpdating = False

Dim Lrs As Long
Lrs = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("A7346:F" & Lrs).ClearContents

Workbooks.Open ("C:\Users\dokat\OneDrive - BIC\BIC\Power BI\Trade Report\Montly Data\2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm")

Dim Lr  As Long
Dim ws As Worksheet
Set ws = Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Worksheets("Summary")
Lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A1:F" & Lr).Copy

Workbooks("Sta P&L Test.xlsm").Sheets("Sta P&L").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Close



Workbooks.Open ("C:\Users\dokat\OneDrive - BIC\BIC\Power BI\Trade Report\Montly Data\2017-2022 6 Year Trended PnL by L3 - February")

Dim Lrm  As Long
Dim wsm As Worksheet
Set wsm = Workbooks("2017-2022 6 Year Trended PnL by L3 - February.xlsm").Worksheets("Summary")
Lrm = wsm.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A2:F" & Lrm).Copy

Workbooks("Sta P&L Test.xlsm").Sheets("Sta P&L").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Workbooks("2017-2022 6 Year Trended PnL by L3 - February").Close




Application.ScreenUpdating = True

End Sub

 

[Dokat]

Hi,

I have below code where i copy paste from two workbooks into third one. First oart of the code works fine however after adding the second part it returns run time error -2147221080. I believe its because of opening second workbook but not sure. Anyone has experience with this priblem and have a solution?


View attachment 60650

Private Sub WorkbookOpen()

Application.ScreenUpdating = False

Dim Lrs As Long
Lrs = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("A7346:F" & Lrs).ClearContents

Workbooks.Open ("C:\Users\dokat\OneDrive - BIC\BIC\Power BI\Trade Report\Montly Data\2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm")

Dim Lr  As Long
Dim ws As Worksheet
Set ws = Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Worksheets("Summary")
Lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A1:F" & Lr).Copy

Workbooks("Sta P&L Test.xlsm").Sheets("Sta P&L").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Close



Workbooks.Open ("C:\Users\dokat\OneDrive - BIC\BIC\Power BI\Trade Report\Montly Data\2017-2022 6 Year Trended PnL by L3 - February")

Dim Lrm  As Long
Dim wsm As Worksheet
Set wsm = Workbooks("2017-2022 6 Year Trended PnL by L3 - February.xlsm").Worksheets("Summary")
Lrm = wsm.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A2:F" & Lrm).Copy

Workbooks("Sta P&L Test.xlsm").Sheets("Sta P&L").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Workbooks("2017-2022 6 Year Trended PnL by L3 - February").Close




Application.ScreenUpdating = True

End Sub

Solved the problem. There was a typo


Below worked.

Private Sub CopyPasteYTD()
On Error Resume Next
Application.ScreenUpdating = False

Dim Lrs As Long
Lrs = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("A7346:F" & Lrs).ClearContents

Workbooks.Open ("C:\Users\dokat\OneDrive - BIC\BIC\Power BI\Trade Report\Montly Data\2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm")

Dim Lr  As Long
Dim ws As Worksheet
Set ws = Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Worksheets("Summary")
Lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A1:F" & Lr).Copy

Workbooks("Sta P&L Test.xlsm").Sheets("Sta P&L").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Close

Workbooks.Open ("C:\Users\dokat\OneDrive - BIC\BIC\Power BI\Trade Report\Montly Data\2017-2022 6 Year Trended PnL by L3 - February.xlsm")

Dim Lrm  As Long
Dim wsm As Worksheet
Set wsm = Workbooks("2017-2022 6 Year Trended PnL by L3 - February.xlsm").Worksheets("Summary")
Lrm = wsm.Cells(Rows.Count, 1).End(xlUp).Row

wsm.Range("A2:F" & Lrm).Copy

Workbooks("Sta P&L Test.xlsm").Sheets("Sta P&L").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Workbooks("2017-2022 6 Year Trended PnL by L3 - February.xlsm").Close


Application.ScreenUpdating = True

End Sub