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

 

[6StringJazzer]

What are the values of LastCopyRow and LastDestRow when the error occurs?

 

[alkyze]

What are the values of LastCopyRow and LastDestRow when the error occurs?

LastCopyRow = 678 and LastDestRow = 2

 

[Fluff]

Not sure you can copy paste between instances like that.
Any reason why you are opening the other workbook in a new instance of Excel?

 

[alkyze]

Just wanted to open it hidden, but I'm open to other alternatives.

 

[Fluff]

Do you just want to paste values?

 

[alkyze]

I need to allow someone to choose the file to open. I then need to copy rows from the spreadsheet they select to the next available row on the destination worksheet.

 

[Fluff]

Do you need to copy formulae formatting etc, or just the cell values?

 

[alkyze]

Just the cell values

 

[Fluff]

In that replace this

wsCopy.Range("A4:AO" & LastCopyRow).Copy  wsDest.Range("A" & LastDestRow)

with

wsDest.Select
wsDest.Range("A" & LastDestRow + 1).Select
ActiveSheet.PasteSpecial Format:="XML Spreadsheet", Link:=False, _
        DisplayAsIcon:=False
objexcel.Application.CutCopyMode = False