Thanks. I'm not sure how you use that type of column reference, I only know how to do it using the column letters. So the example below assumes that the source column is B, and this is the button for row 2:
Code:
Sub Row2Macro ()
Range("REPORT!D5").Value = Range("Database!B2").Value
End Sub
The problem with this is that for each row you will need a new button with a new macro! My first alternative thought was to have a single button at the top of the screen (you could ensure that it was always visible by fixing the top row). Clicking the button would do the action based on the row previously selected. So if you wanted the report based on row 100, click in any cell on row 100, then click the button. The code for this (still assuming a source column of B) would be:
Code:
Sub ReportMacro ()
Range("REPORT!D5").Value = Range("Database!B" & ActiveCell.Row).Value
End Sub
This would mean only one macro was needed.
But if its important to have something on each row, an alternative is to not make it a "real" button. Just colour the cells in a particular column to look like buttons. You can then have a single macro that is triggered whenever any of the cells in the column is selected. In the example code below, I'm assuming that the text in the "fake" button cells is "Click for report":
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "Click for report" Then Range("REPORT!D5").Value = Range("Database!B" & Target.Row).Value
End Sub
This macro must be saved at Database worksheet level. Every time a new cell is selected in the worksheet, the macro runs, and if the selected cell contains the text "Click for report", the value of that row in column B is copied over to the REPORT sheet. Because its only text in a cell, rather than a real button, and only needs a single macro, it can easily be copied down as more rows are added to the Database sheet.