Run-time 1004 Copy Method of Range Class Failed

alkyze

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
What are the values of LastCopyRow and LastDestRow when the error occurs?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
Do you just want to paste values?
 

alkyze

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
Do you need to copy formulae formatting etc, or just the cell values?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
In that replace this
VBA Code:
wsCopy.Range("A4:AO" & LastCopyRow).Copy  wsDest.Range("A" & LastDestRow)
with
VBA Code:
wsDest.Select
wsDest.Range("A" & LastDestRow + 1).Select
ActiveSheet.PasteSpecial Format:="XML Spreadsheet", Link:=False, _
        DisplayAsIcon:=False
objexcel.Application.CutCopyMode = False
 

Watch MrExcel Video

Forum statistics

Threads
1,130,198
Messages
5,640,797
Members
417,166
Latest member
Funwayo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top