VBA Copy/Paste Runtime Error

Question

This block of code keeps receiving a Runtime error 1004. I am trying to copy small blocks of information from one sheet to the other if the beginning column names match.

For a = 8 To 17
    For b = 7 To 21
        If Sheets("Sheet1").Cells(a, 2).Value = Sheets("Sheet2").Cells(b, 1).Value Then
            Sheets("Sheet2").Range(Cells(b, 1), Cells(b, 7)).Copy Sheets("Sheet1").Cells(a, 6)
        End If
    Next b
Next a

How can I debug my code?

Answer 1

In short, there is something wrong with the line

Sheets("Sheet2").Range(Cells(b, 1), Cells(b, 7)).Copy Sheets("Sheet1").Cells(a, 6)

change it into the below should fix your problem.

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(b, 1), Sheets("Sheet2").Cells(b, 7)).Copy Sheets("Sheet1").Cells(a, 6)

---

How did I debug:

When your code is run, click "Debug" when the runtime error 1004 pop up. The 4th line is highlighted in yellow (i.e. VBA encounters error running that line). So that's where we start.

To eliminate problem, we can move the cursor over various variables to look at its value. b = 7, a = 8, seems fine.

Removing the parameters for Copy function, leaving only everything before the .Copy... the problem is still there. So there should be something wrong with

Sheets("Sheet2").Range(Cells(b, 1), Cells(b, 7)).Copy

Looking at help on .Range and note that you need to specify the Sheet as well. And the problem is fixed.

Answer 2

Make sure neither worksheet is protected (not just the range you're copying, but the whole sheet).

Also, how is this called? What's the full function, and is it in Sheet1, ThisWorkbook, or a separate module? Which line is throwing the error?

Try declaring each object separately and see if you can isolate which step fails.

Dim src as Excel.Range
Set src = Sheets("Sheet1").Cells(a, 2)
Dim dst as Excel.Range
Set dst = Sheets("Sheet2").Cells(b, 1)
If src.Value = dst.Value Then
  Dim src2 as Excel.Range
  Set src2 = Sheets("Sheet2").Range(Cells(b, 1), Cells(b, 7))
  Dim dst2 as Excel.Range
  Set dst2 = Sheets("Sheet1").Cells(a, 6)
src2.Copy dst2
End If

Answer 3

I have test different solutions. And after reading the documentation, and test it on my computer, I remembered that:

• By default, Excel VBA passes arguments by reference.
• Cells is initialize once > just one instance

Or, in you solution Cells is called number of times in the same procedure! You pass value twice in a range with same instance by reference. The range get the same instance of object twice > The result is not valid.

You can force passing ByVal with enclosing parameters ( ). This is the same when you used the key WITH

Mime code is :

    Dim shSrc as Excel.Worksheet, shDst as Excel.Worksheet

    Set shSrc = Sheets("Sheet1")
    Set shDst = Sheets("Sheet2")

    'copy
    With shSrc
        .Range((.Cells(1, 1)), (.Cells(1, 7))).Copy
    End With

    'special paste need same range (number of rows and colums)
    With shDst
        .Range((.Cells(1, 1)), (.Cells(1, 7))).PasteSpecial xlPasteValues
    End With