Run Time Error 438 Object Doesnt Support This Property or Method Error

[Dokat]

Hi,

I have a report i would like append data from another workbook. The name of the workbook is "Sta P&L Test, and worksheet is Sta P&L . Data range that i'd like to copy from comes from following workbook ."2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm" Range starts from A2:F to last row. I have below code however when I ran I receive Error 438 message. Did anyone come across same issue and have a solution for it? Thanks

Private Sub WorkbookOpen()

Application.ScreenUpdating = False

Dim LRW As Long

LRW = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Range("A7346:F" & LRW).ClearContents


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

Dim LR As Long

Dim wb As Worksheets

Set wb = Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Worksheeets("Summary")

LR = wb.Cells(Rows.Count, 1).End(xlUp).Row

Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row).Copy

Workbooks("Sta P&L Test.xlsm").Activate

Range("A" & LR + 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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

Application.ScreenUpdating = True

End Sub

 

[Dokat]

Is that the line that caused the error, if so what was the error?

Up to below line code works fine but gives error message starting below line.

Set wb = Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm").Worksheeets("Summary")

 

[Dokat]

I modified the code slightly but still getting Run Time 438 ERROR MESSAGE.

Private Sub WorkbookOpen()

Application.ScreenUpdating = False

Dim LRW As Long

LRW = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Range("A7346:F" & LRW).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 wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("2017-2022 6 Year Trended PnL by L3 - YTD February.xlsm")
Set ws = wb.Worksheeets("Summary")

LR = ws.Cells(Rows.Count, 1).End(xlUp).Row

Range("A2:F" & LR).Copy

Workbooks("Sta P&L Test.xlsm").Activate

Range("A" & LRW + 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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





Application.ScreenUpdating = True

End Sub

 

[RoryA]

Typo:

Set ws = wb.Worksheets("Summary")

not:

Set ws = wb.Worksheeets("Summary")

 

[Dokat]

Typo:

Set ws = wb.Worksheets("Summary")

not:

Set ws = wb.Worksheeets("Summary")

Thank you! I corrected the typo and adjusted the code, now i am not receiving error message for the most part code works however paste it starting wrong row #

Private Sub WorkbookOpen()

Application.ScreenUpdating = False

Dim LRW As Long

LRW = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Range("A7346:F" & LRW).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("A2:F" & LR).Copy



Workbooks("Sta P&L Test.xlsm").Activate

Range("A" & LRW + 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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




Application.ScreenUpdating = True

End Sub

 

[Fluff]

That's because you are calculating the last used row on the summary sheet, not the sheet you are going to paste to.