VBA maco button for copying specific data

[ccharles4432]

Hello,
I am very new to excel and VBA. I am trying to:
-Have a button for each row in a sheet called "sheet one".
-On click for one of these buttons, it copies 10 specific cells from the row (say J, H, I, M and L for a given row),
-Putting them in a new entry in a different sheet "sheet two" (same worksheet).

If anyone could cover how I can copy specific cells for a given row and paste them into a different sheet, that would be the best.
Thanks!

 

[Joe4]

Welcome to the Board!

Instead of having a button for each row, wouldn't it be better to just have one button, and have that button prompt the user which row number they would like to copy over?

Also, regarding just copying over specific columns, which columns should they be pasted to?
Are they going to the same columns (i.e. "J" going to "J", "H" going to "H", etc) or are they going in order (i.e."J" going to "A", "H" going to "B", etc)?
And is the new row of data to be placed underneath the current last row of data on Sheet2?

 

[ccharles4432]

Welcome to the Board!

Instead of having a button for each row, wouldn't it be better to just have one button, and have that button prompt the user which row number they would like to copy over?

Also, regarding just copying over specific columns, which columns should they be pasted to?
Are they going to the same columns (i.e. "J" going to "J", "H" going to "H", etc) or are they going in order (i.e."J" going to "A", "H" going to "B", etc)?
And is the new row of data to be placed underneath the current last row of data on Sheet2?

That could also work! In regards to copying the columns, the locations are arbitrary and don't need to be in the same ones as sheet 1. Also sorry, yes the new row of data is to be placed underneath the last row of data. Thanks heaps!

 

[Joe4]

OK. Try this. You can adjust it to suit your needs (I documented it, so it should hopefully be obvious where you would make the changes).

Sub MyCopy()

    Dim r As Integer
    Dim nr As Integer
    
'   Prompt user on which row to copy from
    On Error GoTo err_chk
    r = InputBox("Enter row number from Sheet1 to copy from")
    
'   Verify entry
    If r < 1 Or r > Rows.Count Then GoTo err_chk
    On Error GoTo 0
    
'   Use column A on Sheet2 to find last row (*CHANGE IF NEEDED)
    nr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy values from column J on Sheet1 to column A on Sheet2
    Sheets("Sheet2").Cells(nr, "A") = Sheets("Sheet1").Cells(r, "J")
'   Copy values from column H on Sheet1 to column B on Sheet2
    Sheets("Sheet2").Cells(nr, "B") = Sheets("Sheet1").Cells(r, "H")
'   Copy values from column I on Sheet1 to column C on Sheet2
    Sheets("Sheet2").Cells(nr, "C") = Sheets("Sheet1").Cells(r, "I")
'   Copy values from column M on Sheet1 to column D on Sheet2
    Sheets("Sheet2").Cells(nr, "D") = Sheets("Sheet1").Cells(r, "M")
    
    MsgBox "Copy complete!"
    
    Exit Sub
    
err_chk:
    MsgBox "You have not entered a valid row number!", vbOKOnly, "ENTRY ERROR!"
    Exit Sub
    
End Sub

 

[ccharles4432]

OK. Try this. You can adjust it to suit your needs (I documented it, so it should hopefully be obvious where you would make the changes).

Sub MyCopy()

    Dim r As Integer
    Dim nr As Integer
   
'   Prompt user on which row to copy from
    On Error GoTo err_chk
    r = InputBox("Enter row number from Sheet1 to copy from")
   
'   Verify entry
    If r < 1 Or r > Rows.Count Then GoTo err_chk
    On Error GoTo 0
   
'   Use column A on Sheet2 to find last row (*CHANGE IF NEEDED)
    nr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
   
'   Copy values from column J on Sheet1 to column A on Sheet2
    Sheets("Sheet2").Cells(nr, "A") = Sheets("Sheet1").Cells(r, "J")
'   Copy values from column H on Sheet1 to column B on Sheet2
    Sheets("Sheet2").Cells(nr, "B") = Sheets("Sheet1").Cells(r, "H")
'   Copy values from column I on Sheet1 to column C on Sheet2
    Sheets("Sheet2").Cells(nr, "C") = Sheets("Sheet1").Cells(r, "I")
'   Copy values from column M on Sheet1 to column D on Sheet2
    Sheets("Sheet2").Cells(nr, "D") = Sheets("Sheet1").Cells(r, "M")
   
    MsgBox "Copy complete!"
   
    Exit Sub
   
err_chk:
    MsgBox "You have not entered a valid row number!", vbOKOnly, "ENTRY ERROR!"
    Exit Sub
   
End Sub

this helps tremendously. thank you very much

 

[Joe4]

You are very welcome!