gheyman
Well-known Member
- Joined
- Nov 14, 2005
- Messages
- 2,341
- Office Version
- 365
- Platform
- Windows
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])"
Any help is appreciated
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])"
Code:
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