Run-time 1004 Copy Method of Range Class Failed

[alkyze]

I have created a button "Load File" which prompts the user to select an Excel file and once selected, its contents will be copied to the next available row in the spreadsheet. My code is below. I've searched for solutions and have modified the code some based on suggestions but am still getting the 1004 Copy Method of Range Class Failed error on the copy line (in red below). Any help would be greatly appreciated.

Sub LoadFile_Click()
    Dim FileToOpen As String
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim wbDest As Workbook
    Dim wsDest As Worksheet
    Dim LastCopyRow As Long
    Dim LastDestRow As Long

    Set objexcel = CreateObject("Excel.Application")
    objexcel.Application.Visible = False

    Set wbDest = ActiveWorkbook
    Set wsDest = wbDest.Worksheets("Claims")
    LastDestRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

    FileToOpen = Application.GetOpenFilename("Excel files (*.xls*), *xls*", , "Browse for your file to import")
    If FileToOpen <> "" Then
        Set wbCopy = objexcel.Workbooks.Open(FileToOpen)
        Set wsCopy = wbCopy.Worksheets(1)
        LastCopyRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
        wsCopy.Range("A4:AO" & LastCopyRow).Copy  wsDest.Range("A" & LastDestRow)
    End If

    wbCopy.Close SaveChanges:=False
End Sub

 

[EXCEL MAX]

Hello Alkyze,
try to change last part of code to look like this...

    LastCopyRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    wsCopy.Activate
    wsCopy.Range("A4:AO" & LastCopyRow).Copy
    wsDest.Range("A" & LastDestRow).PasteSpecial Paste:=xlValues
    End If
    
    wbCopy.Close SaveChanges:=False
     
End Sub

 

[alkyze]

Updated, but now I'm getting a 1004 PasteSpecial method of Worksheet class failed.

 

[Fluff]

Which code are you using?

 

[alkyze]

Went back and double checked the code and the changes worked this time. I must have had something off the first time. Thank you for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[alkyze]

So I'm back....
The code worked for the first file I pulled in, but now fails with 1004 PasteSpecial method of Worksheet class failed. Weird thing is if I hit debug and then F8 through the step it works and the copy completes. Code is below.

    Set objexcel = CreateObject("Excel.Application")
    objexcel.Application.Visible = False

    Set wbDest = ActiveWorkbook
    Set wsDest = wbDest.Worksheets("Claims")
    LastDestRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    Set wsFiles = wbDest.Worksheets("Files Loaded")
    LastFileRow = wsFiles.Cells(wsFiles.Rows.Count, "A").End(xlUp).Offset(1).Row

    FileToOpen = Application.GetOpenFilename("Excel files (*.xls*), *xls*", , "Browse for your file to import")
    If FileToOpen <> "False" Then
        Set wbCopy = objexcel.Workbooks.Open(FileToOpen)
        Set wsCopy = wbCopy.Worksheets(1)
        LastCopyRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
        wsCopy.Activate
        wsCopy.Range("A4:AO" & LastCopyRow).Copy
        wsDest.Range("A" & LastDestRow).PasteSpecial xlPasteValues
        wbCopy.Application.CutCopyMode = False
        wbCopy.Close SaveChanges:=False
    End If

 

[NdNoviceHlp]

Why are you creating another instance of excel and then not using it or quitting it? You should also declare all of your variables. Dave

 

[alkyze]

New instance of Excel is used in the "Set wbCopy =" line. May not be needed. Just wanted to open the other spreadsheet but not show it to the user. Also, all variables are declared at the beginning of the procedure and objects closed and/or set to nothing at the end.

 

[NdNoviceHlp]

Whoops my bad re. not seeing the "Set wbCopy =" line. It doesn't seem necessary and you will still need to quit the application. If U are quitting the application, I'm not sure what's wrong unless there's some more code that U haven't provided and this is actually part of a looping routine for more than file? Dave

 

[alkyze]

Made some additional changes and everything seems to be working fine now. Full code being used is below.

Sub LoadFile_Click()
    Dim FileToOpen As String
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim wbDest As Workbook
    Dim wsDest As Worksheet
    Dim wsFiles As Worksheet
    Dim ws As New Worksheet
    Dim LastCopyRow As Long
    Dim LastDestRow As Long
    Dim LastFileRow As Long
    Dim intStart As Integer
    Dim HoldFileName As String
    Dim objexcel As New Excel.Application

    Set objexcel = CreateObject("Excel.Application")
    objexcel.Application.Visible = False 

    Set wbDest = ActiveWorkbook
    Set wsDest = wbDest.Worksheets("Claims")
    LastDestRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    Set wsFiles = wbDest.Worksheets("Files Loaded")
    LastFileRow = wsFiles.Cells(wsFiles.Rows.Count, "A").End(xlUp).Offset(1).Row

    FileToOpen = Application.GetOpenFilename("Excel files (*.xls*), *xls*", , "Browse for your file to import")
    If FileToOpen <> "False" Then
        Set wbCopy = objexcel.Workbooks.Open(FileToOpen)
        Set wsCopy = wbCopy.Worksheets(1)
        LastCopyRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
        With wsCopy
            .Unprotect
            .Range("A4:AO" & LastCopyRow).Copy
        End With
       wsCopy.Activate
       wsCopy.Range("A4:AO" & LastCopyRow).Copy
       wsDest.Range("A" & LastDestRow).PasteSpecial xlPasteValues
        MsgBox LastCopyRow - 3 & " claim records loaded!"
        intStart = InStrRev(FileToOpen, "\")
        wsFiles.Cells(LastFileRow, 1) = Mid(FileToOpen, intStart + 1)
        wbCopy.Application.CutCopyMode = False
        wbCopy.Close SaveChanges:=False
    End If

    Set wbDest = Nothing
    Set wsDest = Nothing
    Set wbCopy = Nothing
    Set wsCopy = Nothing
    Set objexcel = Nothing

End Sub