Having difficulty copying and pasting on VBA

[arceaf]

Hi,

I currently have a VBA that copies info from one sheet, and pastes i
onto another sheet. The code is bellow. Normally, the text in th
material is pasted onto cells A2, B2 and E2 when I run the macro (
linked it to a button, so it runs when i click the button). I want th
same info to be copied but, insted, I want it to be copied to a ro
below every time I click the button.

For example:
1 click - material is copied and pasted to A2, B2, E2
2nd click - the same material is copied, but onto cell A3, B3, E3
3rd click - the same material is copied, but onto cell A4, B4, E4

Here's the current code I have:




Private Sub CommandButton2_Click()

Range("C2").Copy
Sheets("Recommendations").Range("A2").PasteSpecial Paste:=xlPasteValues
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("Decision Matrix").Range("G55").Copy
Sheets("Recommendations").Range("B2").PasteSpecial Paste:=xlPasteValues
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Sheets("Decision Matrix").Range("H55").Copy
Sheets("Recommendations").Range("E2").PasteSpecial Paste:=xlPasteValues
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End Sub



Thank you for the help

 

[Howard]

Hi,



I currently have a VBA that copies info from one sheet, and pastes it

onto another sheet. The code is bellow. Normally, the text in the

material is pasted onto cells A2, B2 and E2 when I run the macro (I

linked it to a button, so it runs when i click the button). I want the

same info to be copied but, insted, I want it to be copied to a row

below every time I click the button.



For example:

1 click - material is copied and pasted to A2, B2, E2

2nd click - the same material is copied, but onto cell A3, B3, E3

3rd click - the same material is copied, but onto cell A4, B4, E4

Try this.

Option Explicit

Sub YourButton()

Sheets("Sheet1").Range("C2").Copy
Sheets("Recommendations").Range("A100").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues

Sheets("Sheet1").Range("G55").Copy
Sheets("Decision Matrix").Range("B100").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues

Sheets("Sheet1").Range("H55").Copy
Sheets("Decision Matrix").Range("E100").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues

End Sub

Regards,
Howard

 

[Claus Busch]

Hi,

Am Mon, 1 Jul 2013 07:16:03 +0100 schrieb arceaf:

For example:
1 click - material is copied and pasted to A2, B2, E2
2nd click - the same material is copied, but onto cell A3, B3, E3
3rd click - the same material is copied, but onto cell A4, B4, E4

try:

Private Sub CommandButton2_Click()
Dim LRow As Long
Application.ScreenUpdating = False
With Sheets("Recommendations")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Range("C2").Copy
.Range("A" & LRow).PasteSpecial Paste:=xlPasteValues

Sheets("Decision Matrix").Range("G55:H55").Copy
.Range("B" & LRow).PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

try:

Private Sub CommandButton2_Click()
Dim LRow As Long
Application.ScreenUpdating = False
With Sheets("Recommendations")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Range("C2").Copy
.Range("A" & LRow).PasteSpecial Paste:=xlPasteValues

Sheets("Decision Matrix").Range("G55").Copy
.Range("B" & LRow).PasteSpecial Paste:=xlPasteValues
Sheets("Decision Matrix").Range("H55").Copy
.Range("E" & LRow).PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Regards
Claus B.