adding macro with vlookuo based on cell location

[gurohy]

hi

i have a table with unliited rows.
i would like to add to each row a macro button that when i press it it will put the value of a specific column in the row in an other cell (a difeerent sheet). ut is in order to provide a report for the row selected.

furthermore, as this is unlimited table, i need to automatically add such button with each row created. is it possible?

thanks

 

[Trevor_S]

Not knowing how big your table is, you could end up with a lot of buttons! Might an option be to have a single button at the top, and the macro then work on whatever row was active at the time that the button was pressed?

Could you specify the sheet names involved ... also the column required on the source sheet, and the cell to paste into on the target one?

 

[gurohy]

Not knowing how big your table is, you could end up with a lot of buttons! Might an option be to have a single button at the top, and the macro then work on whatever row was active at the time that the button was pressed?

Could you specify the sheet names involved ... also the column required on the source sheet, and the cell to paste into on the target one?

thanks for the reply.

the table is limitless as iadd rows with time. at the moment it is about 300 rows.
i considered an option to do it with a dropdown list combo to select the specific cell, although it is a bit less elegant than a button ( i want to make it easy while looking at a certain row to simply click at the button next to it and execute a report.

the active sheet name: "Database"
the column is inside a table and and referred as "databsetbl[project_name]" (i want to place the button next to it and the it will know to look for the relevant coulmn in the same row
the cell to pase is in sheet named "REPORT"
the cell is "D5".

much appreciate it!

roy

 

[Trevor_S]

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:

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:

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":

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.

 

[gurohy]

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:

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:

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":

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.

Hi,

Thanks
. the third option is very elegant, but i get the following error : "run time error 1004: 'method range of object '_Worksheet' failed.

i tried also the second option but the error i get is :cannot run the macro. the macro may not be available in this workbook or all macros may be disabled"
i tried an easy macro and it worked so macros are available.

thanks for the help!

 

[Trevor_S]

Apologies - I hadn't been able to test the code when I wrote it! In both cases I'd shown the sheet name within the Range brackets, where it should have separate. Here is the revised code for the second two options, I have now been able to test both.

Third option - "fake" button:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "Click for report" Then Sheets("REPORT").Range("D5").Value = Range("B" & Target.Row).Value
End Sub

Second option - single button acting on currently selected row:

Sub ReportMacro()
Sheets("REPORT").Range("D5").Value = Sheets("Database").Range("B" & ActiveCell.Row).Value
End Sub