CODEWITHSUNDEEP
excelvba
excelguy
excelguy

Reputation: 1624

VBA, ThisWorkbook object doesnt support property or method

I am trying to copy from one workbook to another. I first open workbook B and try to copy to workbook A. However I am getting error Object doesnt support this property or method on line ThisWorkbook.Sheets("BusinessDetails").Range("C4").Paste

Shouldn't ThisWorkbook refer to the workbook and module where the code is running? What am I missing here?

Thanks.

  Sub Update()
    
    Dim FilePath As String
    Dim fname As Variant
    Dim myPath As String
    Dim Source As Workbook
    
    
    Application.DisplayAlerts = False
    
    
    LastRowclear = WorksheetFunction.Max(Sheets("BusinessDetails").Cells(Rows.Count, "AF").End(xlUp).Row)
    Worksheets("BusinessDetails").Range("C4:AF" & LastRowclear).Clear
    myPath = "sample path"
    fname = Dir(myPath & "Business_Level_Report*")
    
    Set Source = Workbooks.Open(myPath & fname)
    With Source
    .Activate
    LastRow = WorksheetFunction.Max(Sheets("BusinessDetails").Cells(Rows.Count, "AE").End(xlUp).Row)
    Source.Sheets(1).Range("B4:AE" & LastRow).Copy
    End With
    
    ThisWorkbook.Sheets("BusinessDetails").Range("C4").Paste
    
    Workbooks(fname).Close savechanges:=False
    
    End Sub

Upvotes: 1

Views: 235

Answers (1)

BigBen
BigBen

Reputation: 49998

There's no Range.Paste method. The problem has nothing to do with ThisWorkbook.

I would use the Destination parameter of Range.Copy:

Source.Sheets(1).Range("B4:AE" & LastRow).Copy _
    Destination:=ThisWorkbook.Sheets("BusinessDetails").Range("C4")

though you can also use Range.PasteSpecial with xlPasteAll.

Upvotes: 3

Related Questions