Paste Special in Active Cell using VBA

Question

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

Comments

[Shai Rado]

@RosyaChairani just make sure your ActiveCell is indeed in "Sheet1"

[Admin]

@ShaiRado - Wouldn't the activecell have to be on Sheet1 if the user was clicking a button on Sheet1 (the button code is on Sheet1's codesheet)? Baring someone trying to programatically 'click' the private sub on Sheet1's codesheet when another worksheet was active of course. I suppose that it could fail if an object was selected however.

[Shai Rado]

@Jeeped I was able to have activecell in "sheet2" and press the button on "sheet1", i guess it's not reasonable, but before I get some "wise" comments here I added that

[Admin]

Yes, I suppose that extra thoroughness is necessary to validate the solution. Good answer btw.

[Chronocidal]

This will fail if Selection is not a Range, or produce spurious content if your Selection is a Multi-Area Range. Selection is not the same as ActiveCell