I use the attached code to refresh a pivot table (as part of a longer sequence that updates multiple pivots and produces a report.)
There are multiple items in a Partner Category that are to be excluded each day, but there are also items classified as Misc that are not present in each update. When the pivot updates on days that there are NO Misc items, that option isn't in the pivot. The following day when Misc items show up, the pivot shows them in the choices, but does not check those, creating a discrepancy in the report. I'm trying to update my code to force the pivot to select those when they are present, but my solution creates an error when the MIsc items are NOT present.
How can I handle the exception, selecting the Misc items when they are there, and not looking for them when they are not?
There are multiple items in a Partner Category that are to be excluded each day, but there are also items classified as Misc that are not present in each update. When the pivot updates on days that there are NO Misc items, that option isn't in the pivot. The following day when Misc items show up, the pivot shows them in the choices, but does not check those, creating a discrepancy in the report. I'm trying to update my code to force the pivot to select those when they are present, but my solution creates an error when the MIsc items are NOT present.
How can I handle the exception, selecting the Misc items when they are there, and not looking for them when they are not?
VBA Code:
'Refresh the Merchant processing pivot table, exclude the exclusions, and copy the data
Sheets("Merchant History Pivot").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("pvt_MerchantHistoryPivot").PivotCache.Refresh
ActiveSheet.PivotTables("pvt_MerchantHistoryPivot").PivotFields( _
"Partner Category").CurrentPage = "(All)"
With ActiveSheet.PivotTables("pvt_MerchantHistoryPivot").PivotFields( _
"Partner Category")
.PivotItems("exclude").Visible = False
' The next line is what hangs my code when there are no Misc items:
.PivotItems("Misc").Visible = True
End With
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
'