Run-Time error 438 Oject doesn't support property or mothos when I try to copy a range to Worksheet

Question

This code allows user to select the files they want to merge together, I'm getting an error on the marked line when I try to copy the sheets from the files to the destination workbook (xlBook).
I might have the wrong approach; I've been using a lot of examples from google, with no luck.

Sub complie_Click()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim xlBook, srcBook As Workbook
    Dim fileSlct, pdfDialog As fileDialog
    Dim xlSheet, srcSheet As Worksheet
    Dim xlRow, srcRow, xlColm, srcColm As Long
    Dim fileIdx As Integer
    Dim hdrRang As Range

    Set xlBook = ThisWorkbook.Sheets
    On Error GoTo error

    Set fileSlct = Application.fileDialog(msoFileDialogFilePicker) 'Allows user to select the files/reports
    With fileSlct
        .AllowMultiSelect = True 'Allows for multi seletion
        .Title = "Select target files:"
        .ButtonName = "Open"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xlsb; *.xls; *.xlw"
        .Show
    End With

    If fileSlct.SelectedItems.Count = 0 Then
        MsgBox "No file found that match.", vbExclamation
        Exit Sub
    End If

    For fileIdx = 1 To fileSlct.SelectedItems.Count 'Loops through each of the selected items, and copies them to workbook 
        Set srcBook = Workbooks.Open(fileSlct.SelectedItems(fileIdx)) 
        Set xlSheet = srcBook.ActiveSheet 
        srcRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 
        srcColm = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 
        Range(Cells(1, 1), Cells(srcRow, srcColm)).Copy xlBook.Sheets("Sheet2").Cells(1, 1) 'Error here
    Next fileIdx

    Application.ScreenUpdating = True
error:
    MsgBox Err.Number & " " & Err.Description, vbCritical
End Sub

You seem to think that `xlBook` is `Workbook`. [In fact](https://stackoverflow.com/a/55060194/11683) it's [`Variant/Sheets`](https://learn.microsoft.com/en-us/office/vba/api/excel.sheets), which, indeed, does [not](https://learn.microsoft.com/en-us/office/vba/api/excel.sheets#properties) have a `Sheets` property. — GSerg, Apr 28 '21 at 15:42
xlBook should be the destination workbook. xlSheet is the destination sheet — kaligirl, Apr 28 '21 at 16:01
It's not important what it should be, it's important what it is. In your code, `xlBook` is declared as `Variant`, and the value it contains is `ThisWorkbook.Sheets`. — GSerg, Apr 28 '21 at 16:05

score 0 · Accepted Answer · answered Apr 28 '21 at 16:08

Thanks to @GSerg for pointing out my mistake. It was simply having the wrong property.

Sub complie_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim xlBook, srcBook As Workbook
Dim fileSlct, pdfDialog As fileDialog
Dim xlSheet, srcSheet As Worksheet
Dim xlRow, srcRow, xlColm, srcColm As Long
Dim fileIdx As Integer
Dim hdrRang As Range

Set xlBook = ThisWorkbook 'took out .Sheets
On Error GoTo error

Set fileSlct = Application.fileDialog(msoFileDialogFilePicker) 'Allows user to select the files/reports
With fileSlct
    .AllowMultiSelect = True 'Allows for multi seletion
    .Title = "Select target files:"
    .ButtonName = "Open"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xlsb; *.xls; *.xlw"
    .Show
End With

If fileSlct.SelectedItems.Count = 0 Then
    MsgBox "No file found that match.", vbExclamation
    Exit Sub
End If

For fileIdx = 1 To fileSlct.SelectedItems.Count 'Loops through each of the selected items, and copies them to workbook 
    Set srcBook = Workbooks.Open(fileSlct.SelectedItems(fileIdx)) 
    Set xlSheet = srcBook.ActiveSheet 
    srcRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 
    srcColm = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 
    Range(Cells(1, 1), Cells(srcRow, srcColm)).Copy xlBook.Sheets("Sheet2").Cells(1, 1)
Next fileIdx

Application.ScreenUpdating = True

error: MsgBox Err.Number & " " & Err.Description, vbCritical End Sub

Thanks again GSerg! Now it's just formatting the output.

`Dim xlBook As Workbook, srcBook As Workbook`. You have to specify the type every time, otherwise it's `Variant`. — BigBen, Apr 28 '21 at 16:18
Thank you also BigBen. That will help in future projects so I don't get so many errors. — kaligirl, Apr 28 '21 at 16:22

Run-Time error 438 Oject doesn't support property or mothos when I try to copy a range to Worksheet

1 Answers1