I think the problem is with trying to select a range on another sheet via a
commandbutton (which is an ActiveX control). Seems it would work with a
button from the forms toolbar. I can't explain the technical details, but
you can fix it by not selecting anything (which is unnecessary, slows down
your code, and can make it harder to read).

With Sheets("sheet2")
.Range(.Cells(1, 1), .Cells(10, 10)).Copy
End With
Sheets("Sheet1").Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False



"Sunny" wrote:

On "Sheet1" I have a command button to:
1) copy a range of cells on "Sheet2"
2) paste copied values back into "Sheet1"

The following set of instructions seem to work.

Private Sub CommandButton1_Click()
Sheets("sheet2").Select
Sheets("Sheet2").Range("A1:J10").Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

The instructions below doesn't work.

Private Sub CommandButton1_Click()
Sheets("sheet2").Select
Sheets("Sheet2").Range(Cells(1, 1), Cells(10, 10)).Select 'offending line
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

The offending line is the third line with ".Range(...)" in it. The second
method seems to work if I copy/paste cells from the same sheet.

I would like to use numeric values to set the range because the table of
values that I want to copy on "Sheet2" will change.