Macro to paste data to another sheet without overwriting using command button

[baconbits]

Hello, I have a command button that, when pressed, will copy values(only) from certain cells on sheet1 and paste to certain columns on sheet2, but I'm having trouble with coding the macro.

Data on sheet1 will change, but sheet2 is a log, so will need the macro to paste into next blank row in that column.

sheet1 D10 ---> sheet2 F17 (and then next time I press, it will paste to F18, then F19, and so on, forever)
sheet1 D11 ---> sheet2 H17 (then H18 etc)
sheet1 D13 ---> sheet2 G17 (then G18 etc)
sheet1 G6 ---> sheet2 I17 (then I18 etc)
And so on... There are 8 values to be copy pasted in this fashion.

I would appreciate any help with this. I've searched for answers already but I can only find code that does half the job. The macro must start pasting from the specified cells, and must shift down one row as to not overwrite data.
Thanks very much.

(Excel, Office Pro 2016)

 

[Zot]

The code pattern should be like this. You can add as many as you like

Sub Test()

If Sheet2.Range("F17") = 0 Then
    Sheet2.Range("F17") = Sheet1.Range("D10")
Else
    Sheet2.Cells(Rows.Count, "F").End(xlUp).Offset(1) = Sheet1.Range("D10")
End If

If Sheet2.Range("H17") = 0 Then
    Sheet2.Range("H17") = Sheet1.Range("D11")
Else
    Sheet2.Cells(Rows.Count, "H").End(xlUp).Offset(1) = Sheet1.Range("D11")
End If

End Sub

 

[baconbits]

The code pattern should be like this. You can add as many as you like

Sub Test()

If Sheet2.Range("F17") = 0 Then
    Sheet2.Range("F17") = Sheet1.Range("D10")
Else
    Sheet2.Cells(Rows.Count, "F").End(xlUp).Offset(1) = Sheet1.Range("D10")
End If

If Sheet2.Range("H17") = 0 Then
    Sheet2.Range("H17") = Sheet1.Range("D11")
Else
    Sheet2.Cells(Rows.Count, "H").End(xlUp).Offset(1) = Sheet1.Range("D11")
End If

End Sub

Thanks very much for your reply. This code is almost perfect ! however, it is copying H16 and I16 from sheet2 and pasting to F17 and H17 on sheet1. How do I make it go the other way ?

 

[Zot]

Look at the picture below

This is what you can see in VB Editor. The sheet name that you see in Workbook is the name in Bracket. However, if you read from left to right, Sheet1, Sheet2, Sheet3 and so on refers to the name by sequence (name on the left to bracket). My code actually refers to the sheet sequence, not the sheet name in bracket. You can use sheet name in bracket which is easier to refer.

If Sheets("Sheet2").Range("F17") = 0 Then
    Sheets("Sheet2").Range("F17") = Sheets("Sheet1").Range("D10")
Else
    Sheets("Sheet2").Cells(Rows.Count, "F").End(xlUp).Offset(1) = Sheets("Sheet1").Range("D10")
End If

 

[baconbits]

Thanks very much for your reply. This code is almost perfect ! however, it is copying H16 and I16 from sheet2 and pasting to F17 and H17 on sheet1. How do I make it go the other way ?

NEVERMIND ! yes I had the sheets labelled incorrectly... sheet1 shouldd've been sheet2 and sheet2 should've been sheet3. Perfect. Thank you so much

 

[Zot]

You are welcomed