I am trying to run a quick format on a table that includes adding a Vlookup formula. The formula references a table that comes out of a pivot table, so it has a different name every time. If I look up the new generated table name, and change it below it works. Is there a way to capture the table name and insert it into the formula? Or possibly rename the table to the same thing everytime (which I think is going to cause some conflict).
Thank you
code:
Sub complete()
'
' complete Macro
'
' Keyboard Shortcut: Ctrl+a
'
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:N").Select
Selection.Delete Shift:=xlToLeft
Columns("K:M").Select
Selection.Delete Shift:=xlToLeft
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(Table16)[[#This Row],[SecurityID]], PTR!C[-2]:C, 3, FALSE), 0)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=Table13[[#This Row],[Column1]]/SUM(C[-6])"
Columns("M:M").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
End Sub
Thank you
code:
Sub complete()
'
' complete Macro
'
' Keyboard Shortcut: Ctrl+a
'
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:N").Select
Selection.Delete Shift:=xlToLeft
Columns("K:M").Select
Selection.Delete Shift:=xlToLeft
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(Table16)[[#This Row],[SecurityID]], PTR!C[-2]:C, 3, FALSE), 0)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=Table13[[#This Row],[Column1]]/SUM(C[-6])"
Columns("M:M").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
End Sub