VBA: When refreshing Source Data having trouble with filtered items

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
192
I cannot get the filtered items to refresh.

When I recieve new data each week, i do a clear all then paste and refresh new source data into a defined table. The source data itself refreshes accurately but will not refresh in the PivotItems list. It will add any new items but will not remove previous items in the PivotItems list.

Because I didn't want write the code for 8 pivot tables and delete and remake them, what I did was create a macro that is supposed to go through PivotItems list and make visble or invisible specific items related to the new source data. Kind of a "PivotItems" refresh. It will work for some tables but not others filtered for the same data. Not sure why.

This is a sample code refreshes the PivotItems of 2 of 8 tables. The first one doesn't do it but the second one does. Not sure why? (afterthought, maybe the source data doesn't refresh all the PivotItems lists??)

Code:
Sub ISRPivotUpdater()
Dim ws As Worksheet
Dim pt As PivotTable
Dim a, b, c, d, e, f, g, h As Integer
Dim n, o, p, q, r, s, t, u As Integer
Application.ScreenUpdating = False
'........................................................................ Ticket Quality Pivot Table Updates
                                                                           
'........................................................................ Servicing SCIMs
Set pt = Worksheets("Ticket Quality").PivotTables("ServicingSCIM")
     n = pt.PivotFields("SCIM Component").PivotItems.Count
        For a = 1 To n
          On Error GoTo NoSSCIM
            Select Case pt.PivotFields("SCIM Component").PivotItems(a).Value
                Case "ADTEMPUS-CAT", "ADTEMPUS-DLSS", "ADTEMPUS-ICAT", "BREACH LETTER TOOL", "CAT", "CLIENT SERVICES", _
                "CMTS", "CSADMIN", "CSWEB", "DLSS", "DRDS", "DRI", "EARLY RESOLUTION", "EDGE CHECKWRITER", "EDGE CHECKWRITTER", "ELI", "ESIGNA", "FANDS", "FUNDTRANSFER", _
                "FORTRACS", "FORTRACSHE", "FNCCMS", "GIWS", "ICAT", "ICTL", "LATS", "LAWS", "LENDER LIVE", "LENDERLIVE", _
                "LPSD-DM", "LPSD-DMC", "LPSD-IM", "LPSD-PM", "LSA", "MAC", "MERS", "MSP", "OPUS", "OPUS MRC", _
                "OPUS PCA", "PASSPORT", "PASSPORT (PPT)", "POTS", "PREPAY", "RLOFTS", "RLOTS", "SBO2000", "SIGNPLUS", "WAE", _
                "WEB SECURITY", "WIN CMSS", "WINCMSS", "WIN CMMS": pt.PivotFields("SCIM Component").PivotItems(a).Visible = False
            End Select
        Next a
     
Set pt = Worksheets("Ticket Quality").PivotTables("SSCIMsummary")
     o = pt.PivotFields("SCIM Component").PivotItems.Count
        For b = 1 To o
          On Error GoTo NoSSCIM
            Select Case pt.PivotFields("SCIM Component").PivotItems(b).Value
                Case "ADTEMPUS-CAT", "ADTEMPUS-DLSS", "ADTEMPUS-ICAT", "BREACH LETTER TOOL", "CAT", "CLIENT SERVICES", _
                "CMTS", "CSADMIN", "CSWEB", "DLSS", "DRDS", "DRI", "EARLY RESOLUTION", "EDGE CHECKWRITER", "EDGE CHECKWRITTER", "ELI", "ESIGNA", "FANDS", "FUNDTRANSFER", _
                "FORTRACS", "FORTRACSHE", "FNCCMS", "GIWS", "ICAT", "ICTL", "LATS", "LAWS", "LENDER LIVE", "LENDERLIVE", _
                "LPSD-DM", "LPSD-DMC", "LPSD-IM", "LPSD-PM", "LSA", "MAC", "MERS", "MSP", "OPUS", "OPUS MRC", _
                "OPUS PCA", "PASSPORT", "PASSPORT (PPT)", "POTS", "PREPAY", "RLOFTS", "RLOTS", "SBO2000", "SIGNPLUS", "WAE", _
                "WEB SECURITY", "WIN CMSS", "WINCMSS", "WIN CMMS": pt.PivotFields("SCIM Component").PivotItems(b).Visible = False
            End Select
        Next b
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,123,070
Messages
5,599,611
Members
414,325
Latest member
annamsreenu

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
Top