Command Button Code to copy data

[sho]

I have a spreadsheet where I would like to click a button
and it extracts certain information into a different sheet.

e.g. on Sheet 1 I would like to click a command button
that copies information from cells A1, C1, D1 & E1 and
places this in Sheet 2 cells E5, E7, E8 and E9

Can anyone help with the code to achieve this?

 

[Mike Fogleman]

Sheet("Sheet2").Range("E5").Value = Sheet("Sheet1").Range("A1").Value
etc,etc
Mike F

 

[sho]

Mike,

Thanks for the reply unfortunately this won't work as I
will have more than 1 command button picking up different
cells on sheet 1 (e.g it may be cells A1, C1, D1 & E1 but
next time it may be A6, B6, C6, E6) it will always
populate the same fields on sheet 2 though (cells E5, E7,
E8, E9)

That was why I was looking to do was place a command
button at the end of each row in Sheet 1 so that when I
wish to copy that particuar row, I can press the button at
the end to copy the relevant row to the correct cells in
sheet 2

Hope this makes sense!

 

[Dave Peterson]

Put a button from the Forms toolbar on each row.

then assign each button to this one macro:

Option Explicit
Sub testme()

Dim myRow As Long

myRow = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row

With ActiveSheet
Worksheets("Sheet2").Range("E5").Value = .Cells(myRow, "A").Value
Worksheets("Sheet2").Range("E7").Value = .Cells(myRow, "C").Value
'etc
End With

End Sub

====
An alternative. Put a forms button in Row 1 and make row 1 always visible--
with A1 visible, select B1, window|freeze panes

Then tell your user to select the row first and click the button. Use the
activecell's row to determine the row to copy.

Option Explicit
Sub testme2()

Dim myRow As Long

myRow = Activecell.row

With ActiveSheet
Worksheets("Sheet2").Range("E5").Value = .Cells(myRow, "A").Value
Worksheets("Sheet2").Range("E7").Value = .Cells(myRow, "C").Value
'etc
End With

End Sub