VBA copy range error 1004

Question

I get a 1004 error when running this code:

Dim Row As Integer
Dim Col As Integer

Row = Worksheets("Design").Cells(11, 22).Value
Col = Worksheets("Design").Cells(12, 22).Value

Worksheets("Tablecorrected").Range(Cells(2 + 19 * Row, 1 + 19 * Col), Cells(19 + 19 * Row, 18 + Col * 19)).Copy _
Destination:=Worksheets("Scriptsheet").Range(Cells(1, 1), Cells(18, 18))

It point to the copy line, and I dunno what is wrong here. Thanks for you help

The `Cells()` inside the `Range()` are refering to the active sheet not the sheet to which the `Range()` is referring. You need to qulaify the `Cells()` to the proper sheet. `Worksheets("Tablecorrected").Range(Worksheets("Tablecorrected").Cells(2 + 19 ...` and so on. — Scott Craner, May 12 '16 at 16:15
Like this: `code`Worksheets("Tablecorrected").Range(Worksheets("Tablecorrected").Range(Cells(2 + 19 * Row, 1 + 19 * Col), Cells(19 + 19 * Row, 18 + Col * 19))).Copy _ Destination:=Worksheets("Scriptsheet").Range(Worksheets("Scriptsheet").Range(Cells(1, 1), Cells(18, 18))) `code` — IschaIschratioh, May 12 '16 at 16:20
http://stackoverflow.com/questions/27763089/count-the-number-of-rows-in-another-sheet/27763394#27763394 — , May 12 '16 at 16:43

score 3 · Accepted Answer · answered May 12 '16 at 16:25

The Cells() inside the Range() are referring to the active sheet not the sheet to which the Range() is referring.

You need to qualify the Cells() to the proper sheet.

Worksheets("Tablecorrected").Range(Worksheets("Tablecorrected").Cells(2 + 19 ... and so on.

Or to save typing you would use a With Block

Dim Row As Integer
Dim Col As Integer

Row = Worksheets("Design").Cells(11, 22).Value
Col = Worksheets("Design").Cells(12, 22).Value

With Worksheets("Tablecorrected")

    .Range(.Cells(2 + 19 * Row, 1 + 19 * Col), .Cells(19 + 19 * Row, 18 + Col * 19)).Copy _
    Destination:=Worksheets("Scriptsheet").Range(Worksheets("Scriptsheet").Cells(1, 1), Worksheets("Scriptsheet").Cells(18, 18))

End With

VBA copy range error 1004

1 Answers1