I am having issues with my VBA scripts designed to restructure a PowerPivot pivot table. The issue arises only when the workbook is first opened. I am getting a 1004 error code where the VBA code is trying to add the CubeField measures. If I manually add/remove a measure from the PivotTable field list, the code will then run OK.
I believe it has something to do with the measures not being pre-calculated when you first open the workbook. Does anyone have any workarounds for this?
Here is the code I am running - errors out at the bolded line:
Sub Financial_View()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveWindow.FreezePanes = False
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").ClearTable
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").DisplayEmptyRow = False
ActiveWorkbook.SlicerCaches("Timeline_INVOICE_DATE").TimelineState. _
SetFilterDateRange "1/1/2018", "1/31/2018"
ActiveWorkbook.SlicerCaches("Slicer_ORDER_TYPE").VisibleSlicerItemsList = Array("[Query].[ORDER TYPE].&[CLOSED]")
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields( _
"[QUERY].[CUSTOMER]")
.Orientation = xlRowField
.Position = 1
End With
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields( _
"[QUERY].[CATEGORY]")
.Orientation = xlRowField
.Position = 2
End With
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields( _
"[QUERY].[PART NO]")
.Orientation = xlRowField
.Position = 3
End With
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields( _
"[QUERY].[DESCRIPTION]")
.Orientation = xlRowField
.Position = 4
End With
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields( _
"[Calendar].[YEAR]")
.Orientation = xlColumnField
.Position = 1
End With
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").AddDataField Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields("[MEASURES].[SUM OF INVOICE_QTY]"), "QTY"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").AddDataField Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields("[MEASURES].[SUM OF EXT NET PRICE]"), "REVENUE"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").AddDataField Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields("[MEASURES].[SUM OF EXT GM]"), "GM"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").AddDataField Worksheets("Dealer Install Sales").PivotTables("PivotTable1").CubeFields("[MEASURES].[GM%]"), "GM%"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields("[Query].[CUSTOMER].[CUSTOMER]").AutoSort xlDescending, "[Measures].[Sum of EXT NET PRICE]"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields("[Query].[CATEGORY].[CATEGORY]").AutoSort xlDescending, "[Measures].[Sum of EXT NET PRICE]"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields("[Query].[PART NO].[PART NO]").AutoSort xlDescending, "[Measures].[Sum of EXT NET PRICE]"
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields("[Query].[PART NO].[PART NO]") _
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields( _
"[Measures].[Sum of EXT NET PRICE]")
.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
End With
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields( _
"[Measures].[Sum of EXT GM]")
.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
End With
With Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields( _
"[Measures].[GM%]")
.NumberFormat = "0.00%"
End With
Worksheets("Dealer Install Sales").PivotTables("PivotTable1").PivotFields( _
"[Query].[CUSTOMER].[CUSTOMER]").DrilledDown = False
With Worksheets("Dealer Install Sales")
.Columns("A").ColumnWidth = "30"
.Columns("B").ColumnWidth = "22"
.Columns("C").ColumnWidth = "14"
.Columns("D").ColumnWidth = "30.6"
.Columns("E:O").ColumnWidth = "12"
.Columns("E:P").HorizontalAlignment = xlCenter
End With
Cells(1, "B").Value = "Financial View"
ActiveWorkbook.SlicerCaches("Timeline_INVOICE_DATE").TimelineState. _
SetFilterDateRange "1/1/2016", "12/31/2018"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
' Must be done after screen updating has been turned back on
Worksheets("Dealer Install Sales").Rows(19).Select
ActiveWindow.FreezePanes = True
End Sub
