Object Doesn't Support this Property or Method When copying from another workbook

Question

I'm trying to write a small code in Excel that lets me open in the background another workbook, copy a range of data in there, and then pasty it in the active workboo. Should be pretty straight forward but for some reason I'm getting this error. So far what I've got is this, and I know the error comes from this line "cpyLastRow = ImportBook.cpySheet.Cells(3, 1).End(xlDown).Row", Ive got some variables commented to make it a little bit mor dyanimc in the future. Any ideas?

Private Sub CommandButton2_Click()
Dim OpenFile As Variant
Dim ImportBook As Workbook
Dim cpySheet As Worksheet
Dim cpyLastRow As Long
Dim cpyLastColumn As Long
'Dim cpyStartCell As Range

Set cpySheet = Sheets("DAO")
'Set cpyStartCell = Range("C3")

Application.ScreenUpdating = False
OpenFile = Application.GetOpenFilename(Title:="Select a file to import data", filefilter:="Excel Files (*.xls*),*xls*")
If OpenFile <> False Then
    Set ImportBook = Application.Workbooks.Open(OpenFile)
    cpyLastRow = ImportBook.cpySheet.Cells(3, 1).End(xlDown).Row
    'cpyLastColumn = cpyStartCell.Column
    ImportBook.cpySheet.Range("C3", cpySheet.Cells(cpyLastRow, 3)).Copy
    ThisWorkbook.ActiveSheet.Range("C3").PasteSpecial xlPasteValues
    ImportBook.Close False
End If
Application.ScreenUpdating = True

End Sub

Also please avoid finding last row like this. You may want to see [This](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Siddharth Rout, Apr 23 '21 at 05:01
Is Sheets("DAO") referring to a sheet in the workbook the code is in or the workbook you are opening, or do both workbooks have a sheet named 'DAO'? — norie, Apr 23 '21 at 05:12
In your code cpySheet will refer to the 'DAO' sheet in the either the workbook with the code in it or the workbook that is active when the code is run. Is that the worksheet you want to copy from or is it the 'DAO' sheet in the workbook you are opening you want to copy from? — norie, Apr 23 '21 at 06:08

score 0 · Answer 1 · answered Apr 23 '21 at 04:50

You get an error due to mixing import workbook property and active worbook sheet reference. Try to use method 1 or method 2. Be sure to specify actual sheet name in the import workbook.

'get reference to sheet "ABF - DAO" in active workbook
Set cpySheet = Sheets("ABF - DAO") 
...
'error: mix workbook property and sheet reference
cpyLastRow = ImportBook.cpySheet.Cells(3, 1).End(xlDown).Row

'method 1: get reference to sheet in import workbook
Set cpySheet = ImportBook.Sheets("ABF - DAO")
cpyLastRow = cpySheet.Cells(3, 1).End(xlDown).Row

'method 2: get last row without sheet reference
cpyLastRow = ImportBook.Sheets("ABF - DAO")

score 0 · Answer 2 · answered Apr 23 '21 at 07:54

Copy Column Range From Closed Workbook

Option Explicit

Private Sub CommandButton2_Click()
    
    Const ProcName As String = "CommandButton2_Click"
    On Error GoTo clearError
    
    Const sTitle As String = "Select a file to import data"
    Const sFilter As String = "Excel Files (*.xls*),*xls*"
    Const sName As String = "DAO"
    Const sFirst As String = "C3"
    
    Const dFirst As String = "C3"
    
    Dim dSuccess As Boolean
    
    ' Source
    
    ' Path
    Dim sPath As Variant
    sPath = Application.GetOpenFilename(Title:=sTitle, FileFilter:=sFilter)
    
    If sPath = False Then
         MsgBox "You canceled.", vbExclamation, "Canceled"
         GoTo ProcExit
    End With
    
    Application.ScreenUpdating = False
    
    ' Worksheet
    Dim swb As Workbook: Set swb = Workbooks.Open(sPath)
    On Error Resume Next
    Dim sws As Worksheet: Set sws = swb.Worksheets(sName)
    On Error GoTo 0
    If sws Is Nothing Then
        CloseWithoutSaving swb
        MsgBox "The worksheet '" & sName & "' does not exist.", _
            vbCritical, "No Worksheet"
        GoTo ProcExit
    End If
    
    ' Range
    Dim fCell As Range: Set fCell = sws.Range(sFirst)
    With fCell
        
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        
        If lCell Is Nothing Then
            CloseWithoutSaving swb
            MsgBox "No data.", vbCritical, "No Data"
            GoTo ProcExit
        End If
        
        Dim srg As Range: Set srg = .Resize(lCell.Row - .Row + 1)
    
    End With
    
    ' Destination
    
    ' Assuming that the button is on the Destination Worksheet.
    Dim dCell As Range: Set dCell = Range(dFirst)
    ' Otherwise, you should do something like this:
    'Set dCell = ThisWorkbook.Worksheets("DAO").Range(dFirst)
    
    ' Copy (by Assignment)
    
    dCell.Resize(srg.Rows.Count).Value = srg.Value
    CloseWithoutSaving swb
    dSuccess = True
    
ProcExit:
    
    If Not Application.ScreenUpdating Then
        Application.ScreenUpdating = True
    End If
    
    If dSuccess Then
        MsgBox "Data transferred.", vbInformation, "Success"
    End If
    
    Exit Sub
    
clearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume ProcExit
End Sub

Sub CloseWithoutSaving( _
        ByVal wb As Workbook)
    Application.DisplayAlerts = False
    wb.Close False
    Application.DisplayAlerts = True
End Sub

Object Doesn't Support this Property or Method When copying from another workbook

2 Answers2

Copy Column Range From Closed Workbook