Getting a 1004 error when pasting from one workbook to another

Question

What I'm trying to do is open one workbook, copy all the data on the first sheet of it and then adding that data to the first empty row of a sheet in another workbook. I seem to run into a problem when pasting the data but I don't fully understand why. I have run the code and just copied the top row of a sheet and then used my method of finding the first empty row and pasting it there which has worked, so I must be something with how I'm copying / selecting my date.

Here is the code :

    MyFile6.Activate
MyFile6.Worksheets(1).Activate
Cells.Select
Selection.Copy
Windows("Frávikagreining.xlsm").Activate
Sheets("Laun").Select
Dim Rng As Long
Rng = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(Rng, 1).Select
ActiveSheet.Paste

I have already defined and opened the workbook "MyFile6" (code not shown here). So I copy the data on the first sheet of this workbook MyFile6, then I open the sheet "Laun" at another workbook, find the last used row in column A, go one further down (first empty cell) and select that. But then my paste attempt is stopped by an error.

Any help / better way to do this would be greatly appreciated !

score 0 · Accepted Answer · answered Feb 15 '19 at 12:32

Cells.Select
Selection.Copy

You are getting that error because you are copying ALL Cells but not pasting in A1. And hence the error. You are trying to fit a bigger object into a smaller object. Work with realistic range objects instead of All Cells by finding last row in first sheet as well and then identifying the range to copy and then pasting accordingly.
Also avoid the use of .Select/Activate. You may want to see How to avoid using Select in Excel VBA

Your code can be written as (UNTESTED)

Dim lRow As Long, lCol As Long
Dim rngToCopy As Range
Dim thatWb As Workbook

'~~> Destination Workbook
Set thatWb = Workbooks("Frávikagreining.xlsm")

With MyFile6.Worksheets(1)
    '~~> Find last row and column
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    '~~> Set your range to copy
    Set rngToCopy = .Range(.Cells(1, 1), .Cells(lRow, lCol))
End With

With thatWb.Sheets("Laun")
    '~~> find last row in destination sheet for pasting
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

    '~~> Copy and paste
    rngToCopy.Copy .Range("A" & lRow)
End With

Thanks a lot for the explanation. Having things phrased well for you (me here) is immensely helpful when trying to understand something rather than just starring at the code and think. — HaukurV, Feb 18 '19 at 11:18

Getting a 1004 error when pasting from one workbook to another

1 Answers1