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: