Pivot Table Refresh Error Handling

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
74
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?

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
'
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,532
Members
410,845
Latest member
OldSwimmer1650
Top