CODEWITHSUNDEEP
vbaexcel
sims
sims

Reputation: 145

Excel vba: copy values in a range to another workbook using R1C1 formula

I would like to copy the values in a range of a workbook to another one using the R1C1 formula instead of A1 formula (because I'd like to replace the row value by an integer variable in my code later on).

So this code works for me:

 Sub test()
   Dim x, y As Workbook

   Set x = ThisWorkbook
   Set y = Workbooks.Open("myfilepath")

   x.Sheets(1).Range("A1:B1").Copy
   y.Sheets(1).Range("A32:B32").PasteSpecial

 End Sub

However if I try something like that (which I think is equivalent) it doesn't work (I get a '1004' error at x.Sheets(1).Range(Cells(1, 1), Cells(1, 2)).Copy):

Sub test()
  Dim x, y As Workbook

  Set x = ThisWorkbook
  Set y = Workbooks.Open("myfilepath")

  x.Sheets(1).Range(Cells(1, 1), Cells(1, 2)).Copy
  y.Sheets(1).Range(Cells(32, 1), Cells(32, 2)).PasteSpecial

End Sub

Can someone explain why it doesn't work and how how could use an integer variable to define my row instead of letters?

Upvotes: 2

Views: 5397

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

You have to specify the parent object for the Cells, like

x.Sheets(1).Range(x.Sheets(1).Cells(1, 1), x.Sheets(1).Cells(1, 2)).Copy
y.Sheets(1).Range(y.Sheets(1).Cells(32, 1), y.Sheets(1).Cells(32, 2)).PasteSpecial

For more compact code you may use With blocks:

With x.Sheets(1)
 .Range(.Cells(1, 1), .Cells(1, 2)).Copy
End With
With y.Sheets(1)
 .Range(.Cells(32, 1), .Cells(32, 2)).PasteSpecial
End With

Hope this may help.

Upvotes: 2

Related Questions