Why, when copying a cell, shape, etc in Excel VBA, is ActiveSheet.Cells(i,j).Paste not valid?
Instead I have
Cells(i,j).Select
ActiveSheet.Paste
which works, but why?
Add a comment
|
2 Answers
Because .Paste has to be applied to a SheetObject (as you have written correctly: ActiveSheet.Paste)
Check out the method on MSDN. It has to be used the following way:
Worksheets("Sheet1").Range("C1:C5").Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5")
Or more shortly:
Cells(j,i).Copy Destination:=Cells(y,z)
Or use the PasteSpecial-method. It can be applied to Range-objects:
With sheet
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With
Comments
You don't have to tell it to paste for example, if you have the variables set for i and j then you can use this
Range("A1").Copy Cells(i, j)
You could even have no copying at all, such as:
Cells(i, j)=Range("A1")
