Copy and paste to destination with variables?
I don't understand the syntax. I've searched and tried probably 20 different ways to write this. Can anyone help me fix my syntax or something else that the problem is in the destination of my copy? Here is the code -
Dim wksMain As Worksheet, wksStats As Worksheet, i As Long, x As Integer
Set wksMain = Sheets("Main")
Set wksStats = Sheets("Stats")
For x = 1 To 3
i = 100000000
wksMain.Range("C3") = i * x
wksMain.Range("C4") = i * x + i
wksMain.Calculate
wksStats.Cells(1, x + 1).Value = wksMain.Cells(8, 4).Value
wksMain.Range(Cells(16, 6), Cells(37, 6)).Copy _
Destination:=wksStats.Range(Cells(2, x + 1), Cells(24, x + 1))
Next x
From comments
The error I am getting is this Run-time error '1004' Method 'Range' of object'_Worksheet 'failed'
+3
source to share
1 answer
You didn't mention what errors you are getting and on which line, but I see a problem with
wksMain.Range(Cells(16, 6), Cells(37, 6)).Copy _ Destination:=wksStats.Range(Cells(2, x + 1), Cells(24, x + 1))
And that's because you are not fully qualifying your object in the cell.
Try this (untested)
wksMain.Range(wksMain.Cells(16, 6), wksMain.Cells(37, 6)).Copy Destination:= _ wksStats.Range(wksStats.Cells(2, x + 1), wksStats.Cells(24, x + 1))
If you don't assign your object to cells, they will always reference the active sheet.
+3
source to share