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

 

[Fluff]

It would help in future if you could say where the error occurred.
However this Dim wb As Worksheets should be Worksheet singular

 

[Dokat]

It would help in future if you could say where the error occurred.
However this Dim wb As Worksheets should be Worksheet singular

Thanks for your reply I changed it to Worksheet. Now after it runs following code

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

it returns Run Time Error 1004. Thanks

 

[Fluff]

I can see no reason why you would get that error on that line. What is the value of LRW when you get the error?

 

[Dokat]

I can see no reason why you would get that error on that line. What is the value of LRW when you get the error?

LRW row # is 7345. Thanks

 

[Fluff]

Do you have only the one sheet in the workbook that contains the code?

 

[Dokat]

Do you have only the one sheet in the workbook that contains the code?

Yes i do. Under "Sta P&L" sheet

 

[Dokat]

Do you have only the one sheet in the workbook that contains the code?

I ran the code line by line and it returns error after running below line.

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")

 

[Fluff]

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

 

[Dokat]

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

I am not sure if that line is what causing the error. That line opens up another workbook which it executes. But after opening the other workbook i receive below error message.