Copy Paste using Command button in VBA

[Swarup]

Hi Team,
I used the below code but showed error. Can you identify the mistake and provide correct code.
I want to copy values in range C1:C13 & D1:D13 from Sheet1 to Sheet2 in same workbook using command button.

Private Sub CommandButton1_Click()
Dim xSWName As String
Dim xSheet As Worksheet
Dim xPSheet As Worksheet
Dim xIntR As Integer
xSWName = "Sheet2"
On Error Resume Next
Application.ScreenUpdating = False
Set xSheet = ActiveSheet
If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
xSheet.Range("c1:C13 ").Copy
xSheet.Range("d1:13 ").Copy
Set xPSheet = Worksheets.Item(xSWName)
xIntR = xPSheet.UsedRange.Rows.Count
xPSheet.Range("c1:c13").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
xPSheet.Range("d1:d13").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End If
Application.ScreenUpdating = True
End Sub

 

[RobP]

Hi,

heres your code, just a little modified. I commented out things not needed, and amended your ranges (as you don't need to copy twice - you can do it all in one go.

I'm not sure why you need xIntR as a row count, as you do not appear to be using it anywhere within this routine - so I removed it.

If you want to know the last row for some other reason (keep in mind this sub is private, so you won't be able to use It in any other macro I think), you could use :

xIntR = Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row 'uses col 3 to get last row of data

Private Sub CommandButton1_Click()
Dim xSWName As String
Dim xSheet As Worksheet
Dim xPSheet As Worksheet
Dim xIntR As Integer
xSWName = "Sheet2"
On Error Resume Next
Application.ScreenUpdating = False
Set xSheet = ActiveSheet
If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
xSheet.Range("c1:D13").Copy
'xSheet.Range("d1:13 ").Copy
Set xPSheet = Worksheets.Item(xSWName)
'xIntR = xPSheet.UsedRange.Rows.Count
xPSheet.Range("c1:D13").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'xPSheet.Range("d1:d13").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End If
Application.ScreenUpdating = True
End Sub

 

[Swarup]

Thank you.