Search code examples
vbaexcelcopy-paste

Paste Special in Active Cell using VBA

I want to copy a table (range A1 to AV3) from Worksheet 2 to Active Cell in Worksheet 1

my current code is:

Private Sub CommandButton1_Click()
Worksheets("Sheet2").Range("A1:AV3").Copy
Worksheets("Sheet1).ActiveCell.PasteSpecial Paste:=xlPasteFormats
Worksheets("Sheet1").ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub

can anyone help me to fix this? I want every time I click the button, it will copy to any active cell.

Solution

  • Assuming your ActiveCell is in "Sheet1", and the Command-Button is also in "Sheet1", there's no need to add Worksheets("Sheet1") before ActiveCell, just use something like the code below:

    Private Sub CommandButton1_Click()

Worksheets("Sheet2").Range("A1:AV3").Copy
ActiveCell.PasteSpecial xlPasteFormats
ActiveCell.PasteSpecial xlPasteValues

End Sub