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

Sub hardcode()

Dim rownum As Integer

rownum = 4

Do While Range("F" & rownum).Value <> ""

    If Range("F" & rownum).Value = "Match" Then
        Range("E" & rownum & ":f" & rownum).Copy
       Range("E" & rownum).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    rownum = rownum + 1
Loop

End Sub

[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

Sub hardcode()

Dim rownum, colnum, startrow As Integer
Dim Helpcolnum, salescolnum As Integer

'find table header search 20 column by 10 rows looking for column headers "Helper Column" and "Sales"
For colnum = 0 To 19
    For rownum = 0 To 9
        If Range("A1").Offset(rownum, colnum).Value = "Helper Column" Then
            Helpcolnum = colnum
            startrow = rownum
        End If
    
    
        If Range("A1").Offset(rownum, colnum).Value = "Sales" Then
            salescolnum = colnum
        End If

    Next rownum
Next colnum
    

rownum = startrow

Do While Range("A1").Offset(rownum).Value <> ""

    If Range("a1").Offset(rownum, Helpcolnum).Value = "Match" Then
        Range("a1").Offset(rownum, Helpcolnum).Copy
         Range("a1").Offset(rownum, Helpcolnum).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("a1").Offset(rownum, salescolnum).Copy
         Range("a1").Offset(rownum, salescolnum).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
    End If
    rownum = rownum + 1
Loop

End Sub

[demiwen12]

Thank you!