Formula To Reference Changing name of Table

[bjbenny1]

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

 

[DonkeyOte]

Your first formula is missing an opening parenthesis after mention of VLOOKUP.

 

[bjbenny1]

Got it...with one small problem. When the formula comes out for the Sum it has an extra ()....ie (SUM(GG))) instead of (SUM(G:G)) and on the Vlookup the same thing on the Range PTR!JJ) instead of PTR!J:J.....with those small fixes I am in Business....

Sub Quickie()
Dim rngArea As Range, lngArea As Long, strTable As String
Application.ScreenUpdating = True
On Error Resume Next
strTable = ActiveSheet.ListObjects(1).Name
On Error GoTo 0
If strTable = "" Then GoTo ExitPoint
Set rngArea = Range("A:B,G:G,K:K,M:R,U:W")
For lngArea = rngArea.Areas.Count To 1 Step -1
    rngArea.Areas(lngArea).Delete
Next lngArea
With ActiveSheet.ListObjects(1)
    lngCols = .ListColumns.Count
    For bCol = 1 To 2
        .ListColumns.Add
        If bCol = 1 Then
            strFormula = "=" & "IFERROR(Vlookup(" & strTable & "[[#This Row],[SecurityID]],PTR!J:L,3,FALSE),0)"
        Else
            strFormula = "=" & strTable & "[[#This Row],[Column1]]/(SUM(G:G))"

   End If
        Cells(2, lngCols + bCol).FormulaR1C1 = strFormula
    Next bCol
End With
ExitPoint:
Application.ScreenUpdating = True
End Sub

 

[DonkeyOte]

Try using R1C1 notation.... ie

PTR!C10:C12

SUM(C7)

 

[bjbenny1]

You are my Hero!!! Works like a charm!!!