Run time error 1004

[gheyman]

This is causing an error on these two rows. Not sure why. I checked the names in the formula and everything looks OK from what I see

Sheets("PT Material Assoc Costs").ListObjects("PT_MaterialAssocCosts").ListColumns("Mtrl Consolidation").DataBodyRange.FormulaR1C1 = "=XLOOKUP([@[TASK ID]],PQ Cost Source[TASK ID],PQ Cost Source[Mtrl Consolidation])"

Sheets("PT Material Assoc Costs").ListObjects("PT_MaterialAssocCosts").ListColumns("Vendor Name").DataBodyRange.FormulaR1C1 = "=XLOOKUP([@[Ref ID]],PQ_Material_Assoc_Costs[Ref ID],PQ_Material_Assoc_Costs[Vendor Name])"

Sub CreatePivotTableMaterialAssocCost()

'Clear the PT Table

Sheets("PT Material Assoc Costs").Visible = True

    Dim CLR33 As Long
    
    CLR33 = Sheets("PT Material Assoc Costs").Cells(Rows.Count, "A").End(xlUp).Row
    If CLR33 > 2 Then Sheets("PT Material Assoc Costs").Range("A3:AB" & CLR33).ClearContents


'Move Data from the PP Template Material Assoc Cost to create a table for PQ.  This is needed so that the Consolidate Material ID can be added.
    Dim PPLR3 As Long
    PPLR3 = Sheets("Material Assoc Costs").Cells(Rows.Count, "A").End(xlUp).Row
    
    Sheets("PT Material Assoc Costs").Select
    Columns("Y:APN").Select
    Selection.Delete Shift:=xlToLeft
     

'Copy from PP template
    Sheets("Material Assoc Costs").Range("A2:X" & PPLR3).Copy Sheets("PT Material Assoc Costs").Range("A1")
    
 'Add a new column at the end
    With Sheets("PT Material Assoc Costs").ListObjects("PT_MaterialAssocCosts").HeaderRowRange
        With .Offset(0, .Count).Resize(1, 2)
            .Value = Array("Mtrl Consolidation", "Vendor Name")
            .EntireColumn.AutoFit
        End With
    End With
    
Sheets("PT Material Assoc Costs").Select
    
    Sheets("PT Material Assoc Costs").ListObjects("PT_MaterialAssocCosts").ListColumns("Mtrl Consolidation").DataBodyRange.FormulaR1C1 = "=XLOOKUP([@[TASK ID]],PQ Cost Source[TASK ID],PQ Cost Source[Mtrl Consolidation])"
    Sheets("PT Material Assoc Costs").ListObjects("PT_MaterialAssocCosts").ListColumns("Vendor Name").DataBodyRange.FormulaR1C1 = "=XLOOKUP([@[Ref ID]],PQ_Material_Assoc_Costs[Ref ID],PQ_Material_Assoc_Costs[Vendor Name])"

    Sheets("Step 5").Select
    Sheets("Step 5").PivotTables("PivotTable2").PivotCache.Refresh


End Sub

Any help is appreciated

 

[RoryA]

The first line can't be the right formula as you have spaces in the source table name.

 

[gheyman]

Thanks, Rory. Sometimes its right in front of your face and you don't see it. Thank you!