CODEWITHSUNDEEP
excelvba
Chris2015
Chris2015

Reputation: 1050

Copy and Paste to Destination with Variables?

I'm at a loss with the syntax on this. I've searched and tried probably 20 different ways to write this. How can I fix my syntax or whatever the problem is on 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 that I am getting is Run-time error '1004' Method 'Range' of object'_Worksheet 'failed'

Upvotes: 3

Views: 1036

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

You have not mentioned what errors are you getting and at what line but I can 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 is because you are not fully qualifying your cells object.

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 do not qualify your cells object then they will always refer to the active sheet.

Upvotes: 3

Related Questions