VBA Code to Paste Values a Table Column Based on Values in Another Column

[demiwen12]

Hello - I need some help with a code that, through clicking on a button, will do the following:

If the cells in Table1 "Helper Column" = "Match", then:
1) Those cells in Table1 "Helper Column" will paste value into the same cells (i.e., remove the formulas and hard code them); and
2) The corresponding cells in Table1 "Sales" will also paste value in the same cells.

With the attached workbook example, this would mean F8:F10 and E8:E10 will be hard coded per #1 and #2 above, respectively.

Thank you in advance!!

[Crooza]

Give this a go

Please Login or Register  to view this content.

[demiwen12]

Thank you! This works perfectly, but I need one tweak. Instead of referencing the range by Column E and F, I want to reference by the table header name (in case new columns are inserted to the left of the table). I tried to replace by the header name ("Helper Column" and "Sales"), but it didn't work.

[Crooza]

Will the header names always be in the same row?

[demiwen12]

Not necessarily, in case new rows are added above. Is it possible to reference the table as a named range in the code so that the location of the table doesn't matter (i.e., regardless of where it is placed)?

[Crooza]

Try this. Its not real elegant but it searches a grid 20 columns wide and 10 rows deep from cell A1 looking for the table header columns then does its thing

Please Login or Register  to view this content.

[demiwen12]

Thank you!