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.
Rich (BB code):
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
Last edited by a moderator: