Pivot Table Refresh Error Handling

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
109
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
'
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top