Copy paste to another sheet

  • Thread starter Thread starter HrvojeZagi
  • Start date Start date
H

HrvojeZagi

Hi,

I would like to copy, paste data from one sheet to another sheet.
I have made command button with this code, and it doesn't work. If someone
can help?

Range("A4:C4").Select
Selection.Copy
Sheets("Data").Select
Range("A2").Select ' code stops on this sentence (even if I put range
A2:C2)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Thanks in advance
 
Create a command button from the Forms toolbar rather than the Controls
Toolbox toolbar, and assign your macro to it.
 
My bet is you used a commandbutton from the control toolbox toolbar as your
button.

If you did, then this code is behind the worksheet.

Unqualified range references in a General module refer to the activesheet. But
when the code is behind the worksheet, this unqualified range refers to the
sheet that owns the code.

And when you do "Range("a2").select", xl thinks you mean the sheet that owns the
code--not the DATA sheet. And you can't select a cell on a sheet that isn't
active.

You could drop the .selects and use something like:

Option Explicit
Private Sub CommandButton2_Click()
Me.Range("A4:C4").Copy
Sheets("data").Range("A2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

In fact, since you're pasting values, you can just assign the values, too:

Option Explicit
Private Sub CommandButton1_Click()
Dim rngToCopy As Range
Set rngToCopy = Me.Range("A4:C4")
With rngToCopy
Worksheets("data").Range("a2") _
.Resize(.Rows.Count, .Columns.Count).Value _
= rngToCopy.Value
End With
End Sub
 
Back
Top