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

baconbits

New Member
Joined
Apr 27, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,385
Office Version
  1. 2016
Platform
  1. Windows
The code pattern should be like this. You can add as many as you like
VBA Code:
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
 
Solution

baconbits

New Member
Joined
Apr 27, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
The code pattern should be like this. You can add as many as you like
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,385
Office Version
  1. 2016
Platform
  1. Windows
Look at the picture below
1619583992212.jpeg


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.

VBA Code:
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

New Member
Joined
Apr 27, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,385
Office Version
  1. 2016
Platform
  1. Windows
You are welcomed
 

Watch MrExcel Video

Forum statistics

Threads
1,133,625
Messages
5,659,941
Members
418,538
Latest member
alc51103

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top