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 MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
Hello Alkyze,
try to change last part of code to look like this...
VBA Code:
    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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

alkyze

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Updated, but now I'm getting a 1004 PasteSpecial method of Worksheet class failed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,425
Office Version
  1. 365
Platform
  1. Windows
Which code are you using?
 

alkyze

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,425
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted it & thanks for the feedback.
 

alkyze

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

VBA Code:
    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
 
Last edited by a moderator:

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,767

ADVERTISEMENT

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

Well-known Member
Joined
Nov 9, 2002
Messages
2,767
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

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Made some additional changes and everything seems to be working fine now. Full code being used is below.

VBA 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 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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,374
Messages
5,624,323
Members
416,020
Latest member
ANDREAC247

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