VBA copy to another sheet not working

[Edjas]

Hi there,

Im pretty new to this and i can't figure someting out. I have the next piece of code:
Private Sub CheckBox1_Click()

i = Worksheets("Checklist").Cells(10, 15)
If CheckBox1 = True And i > 0 And i < 13 Then
Worksheets("Tabellen").range(Cells(45, 1), Cells(47 + i, 7)).Copy Worksheets("Tabellen").range(Cells(45, 10), Cells(47 + i, 16))
End If

End Sub

This code works fine, but if i change the worksheet after the copy to "Checklist":

(Worksheets("Tabellen").range(Cells(45, 1), Cells(47 + i, 7)).Copy Worksheets("Checklist").range(Cells(45, 10), Cells(47 + i, 16))
I get error code 1004
Other copy codes works fine if I want to copy to another worksheet, for example:

If CheckBox8 = True Then
Worksheets("Tabellen").range("E75:H87").Copy Worksheets("P8").range("E27:H39")
Else
Worksheets("P8").range("E27:H39").Clear
End If

but I can't figure out why the first one doesn't work.

I hope someone can help me with this.

 

[Norie]

You need worksheet references for every instance of Cells in the code.

Mind you for the destination you can use the top-left cell rather than specifying the whole range.

Try this.

With Worksheets("Tabellen")
    .Range(.Cells(45, 1), .Cells(47 + i, 7)).Copy Worksheets("Checklist").Cells(45, 10)
End With

PS You might also want to look at using Resize, for example this,

.Range(.Cells(45, 1), .Cells(47 + i, 7))

can be written as this.

Cells(45, 1).Resize(3 + i, 7)