Copy data from one worksheet to another worksheet using a button

[Whitena]

Hi there, im working on a small project with a friend of mine. We are struggling to copy data from one worksheet to another worksheet.
We want the data so when you click on the button on sheet 1 it will insert the specific data into Sheet 2, and if you press that button again, it will insert under the previous data.

This code works great for transferring data from Sheet1 to Sheet2, However id like it to paste the new data underneath the previous data.

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")
Application.CutCopyMode = False


End Sub


Thank you i hope you could help

 

[VoG]

Try

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1)
Application.CutCopyMode = False
End Sub

 

[Whitena]

Hi is it possible i could transfer certain cells, For instance. If i wanted to transfer cells B2, B3, B4, B5, C2, C3, C4, C5, D2, D3, D4, D5.
Also when i transfer data i dont want the format to follow only the data.

Thanks

 

[VoG]

Maybe like this

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("B2,B3,B4,B5,C2,C3,C4,C5,D2,D3,D4,D5").Copy
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Whitena]

Says to debug Line 2, which i cant see anything wrong with :/

Private Sub CommandButton2_Click()
Sheets("Sheet1").Range("B2,B3,B4,B5,C2,C3,C4,C5,D2,D3,D4,D5").Copy
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[VoG]

It compiles OK here.

 

[Whitena]

I dont think my question was clear. sorry my fault it does work. What i mean in my question was say if i had a Range of cells from A1-C10. However i only wanted to paste certain cells such as A5,B7,C8.

Hopefully that makes more sense, sorry for annoying you like this.

 

[VoG]

Try like this

Private Sub CommandButton2_Click()
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("B2").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("B3").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("B4").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("B5").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("C2").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("C3").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("C4").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("C5").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("D2").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("D3").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("D4").Value
Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet1").Range("D5").Value
End Sub

 

[Whitena]

Thank you very much, I am so grateful for your time, i cant thank you enough. Works a treat, now onto the next tasks, wish me luck. Again Thank you VoG