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?

Solution

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.

Comments

[user348631]

I am using a button to use the macro, which does a large amount of operations, this being the problematic one! It is in sheet1 of the same workbook. The error is where the post above mentioned.

[user348631]

This helped tremendously! However I did not run into the second problem you have, it works with your first fix.

[Kenneth L]

@user348631 Oops maybe I did not make it clear enough. I wrote the second part to explain the steps I used to figure out the answers to your problem only... wish you find it useful in the future in solving similar problems ;)

[Dave]

@user348631, don't forget, if it's helpful, upvote it. If it's the answer, mark it (the tick on the side of the post). Or, do both! :)