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?


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.

  • This helped tremendously! However I did not run into the second problem you have, it works with your first fix. – user348631 Jul 23 '14 at 13:16
  • @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 ;) – Kenneth L Jul 24 '14 at 1:23
  • @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! :) – Dave Jul 24 '14 at 7:47

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
  • 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 Jul 23 '14 at 13:01

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")

    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.