VBA: When refreshing Source Data having trouble with filtered items

[ez08mba]

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??)

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