Sub LaborPT() ' create pivot table for Labor
Dim WS As Worksheet, PvtTbl As PivotTable, i As Integer, rng As Range, rng2 As Range
On Error Resume Next
Sheets("LaborPT").Delete
With ThisWorkbook
Set WS = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
End With
WS.Name = "LaborPT"
With ActiveWorkbook
.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Import!R1C1:R1048576C201", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="LaborPT!R1C1", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion15
End With
Sheets("LaborPT").Sort.SortFields.clear
Application.DeleteCustomList Application.CustomListCount
Set rng = Worksheets("Gen Cond").Range("ServiceTypeSort")
Set rng2 = Worksheets("Gen Cond").Range("WallSort")
Application.AddCustomList rng
Application.AddCustomList rng2
With Sheets("LaborPT").PivotTables("PivotTable3")
.PivotCache.Refresh
.PivotFields("Itm Alt").Orientation = xlRowField
.PivotFields("Itm Alt").Position = 1
.PivotFields("Itm Mat Name").Orientation = xlRowField
.PivotFields("Itm Mat Name").Position = 2
.PivotFields("Double Wall").Orientation = xlRowField
.PivotFields("Double Wall").Position = 3
.PivotFields("Sheet").Orientation = xlRowField
.PivotFields("Sheet").Position = 4
.PivotFields("Phase").Orientation = xlRowField
.PivotFields("Phase").Position = 5
.PivotFields("Zone").Orientation = xlRowField
.PivotFields("Zone").Position = 6
.PivotFields("Itm Section Name").Orientation = xlRowField
.PivotFields("Itm Section Name").Position = 7
.PivotFields("Itm Service Type").Orientation = xlRowField
.PivotFields("Itm Service Type").Position = 8
.PivotFields("Itm Spec Abbr").Orientation = xlRowField
.PivotFields("Itm Spec Abbr").Position = 9
.PivotFields("Itm Gauge").Orientation = xlRowField
.PivotFields("Itm Gauge").Position = 10
.PivotFields("Itm Qty").Orientation = xlRowField
.PivotFields("Itm Qty").Position = 11
.PivotFields("Itm CL Len").Orientation = xlRowField
.PivotFields("Itm CL Len").Position = 12
.PivotFields("Itm Weight").Orientation = xlRowField
.PivotFields("Itm Weight").Position = 13
.PivotFields("Connection Grouping").Orientation = xlRowField
.PivotFields("Connection Grouping").Position = 14
.AddDataField Sheets("LaborPT").PivotTables("PivotTable3").PivotFields("Itm Qty"), "Sum of Itm Qty", xlSum
.AddDataField Sheets("LaborPT").PivotTables("PivotTable3").PivotFields("Itm CL Len"), "Sum of Itm CL Len", xlSum
.AddDataField Sheets("LaborPT").PivotTables("PivotTable3").PivotFields("Itm Weight"), "Sum of Itm Weight", xlSum
.AddDataField Sheets("LaborPT").PivotTables("PivotTable3").PivotFields("Calculated E Time"), "Sum of Calculated E Time", xlSum
.PivotFields("Itm Alt").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Mat Name").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Section Name").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Phase").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Service Type Combined").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Zone").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Sheet").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Service Type").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Double Wall").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Mat + Gauge").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Connection Grouping").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Spec Abbr").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Gauge").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Itm Alt").PivotItems("(blank)").Visible = False
.PivotFields("Itm Service Type").PivotItems("Hanger").Visible = False
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.PivotFields("Itm Alt").RepeatLabels = True
.PivotCache.Refresh
.SortUsingCustomLists = True
.PivotFields("Itm Service Name").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=6
.PivotFields("Double Wall").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=7
.SaveData = False
End With
ActiveWorkbook.ShowPivotTableFieldList = False
With Sheets("LaborPT").PivotTables("PivotTable3").PivotFields("Double Wall")
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = False
.PivotItems("Single Wall").Visible = True
.PivotItems("Double Wall").Visible = True
Next i
End With
Sheets("LaborPT").Cells.EntireColumn.AutoFit
Sheets("LaborPT").Sort.SortFields.clear
Application.DeleteCustomList Application.CustomListCount
End Sub
Bookmarks