Wanted: VBA code to "show all" (remove filters) in a pivot table

[mick0005]

Wanted: VBA code to "show all" (remove filters) in a pivot table.

Good afternoon everyone! I am hoping someone could provide some VBA code which will effectively remove all of the filters (or show all) data in a pivot table. The problem is that on a pivot table in excel 2003, you need to go in and unfilter (click show all) on every field in order to get the data back to being whole. (and excel doesn't even make filtered fields filter arrows blue for pivot tables!!)

I find all of the time people use the spreadsheet and then don't remove the filters before they save. When others go in, they don't realize that the pivot table is missing data from the original source because it is filtered.

I plan on assigning the macro to a button, so it doesn't need any code to execute it at a special time.

 

[AlphaFrog]

As a test, can you run this code?
Report what Pivot Field and Pivot Item in that field it errors on.
(No need to include the code in the reply.)

Sub Pivot_ShowAll()
 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    Dim strDateFormat As String
 
    Application.ScreenUpdating = False
    
    [COLOR="Red"]On Error GoTo Handler[/COLOR]
 
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        For Each pf In pt.PivotFields
            If pf.DataType = xlDate Then
                strDateFormat = pf.NumberFormat
                pf.NumberFormat = "0"
            End If
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
            If pf.DataType = xlDate Then pf.NumberFormat = strDateFormat
        Next pf
        pt.ManualUpdate = False
    Next pt
 
    Application.ScreenUpdating = True
    
    Exit Sub

[COLOR="Red"]Handler:
    MsgBox "Pivot field: " & pf.Name & vbCr & "Pivot Item: " & pi.Name, , "Error"[/COLOR]
 
End Sub

 

[mick0005]

As a test, can you run this code?
Report what Pivot Field and Pivot Item in that field it errors on.
(No need to include the code in the reply.)

Sub Pivot_ShowAll()
 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    Dim strDateFormat As String
 
    Application.ScreenUpdating = False
    
    [COLOR=Red]On Error GoTo Handler[/COLOR]
 
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        For Each pf In pt.PivotFields
            If pf.DataType = xlDate Then
                strDateFormat = pf.NumberFormat
                pf.NumberFormat = "0"
            End If
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
            If pf.DataType = xlDate Then pf.NumberFormat = strDateFormat
        Next pf
        pt.ManualUpdate = False
    Next pt
 
    Application.ScreenUpdating = True
    
    Exit Sub

[COLOR=Red]Handler:
    MsgBox "Pivot field: " & pf.Name & vbCr & "Pivot Item: " & pi.Name, , "Error"[/COLOR]
 
End Sub

Here are the results:

Pivot Field: Active Indicator
Pivot Item: Previous LOS

I don't know if it matters, but several of the source columns that the pivot table is based on have formulas in them. Active Indicator is one of them. Its an IF(AND formula

 

[AlphaFrog]

This is what I think is happening but it is a guess.

You created the Pivot Table when one of the Active Indicator formulas had a Previous LOS as a result. You then changed the data and now there is no Previous LOS result in the Active Indicator column. You then refreshed the Pivot Table data.

In the refreshed pivot table, there is still a Previous LOS item in the Active Indicator field, but there is no Previous LOS data to actually select. This is what's causing the error when trying to reset the Previous LOS item to .Visible = True.

If this is actually the case, I think the simplest way around it is to just ignore the error.

Sub Pivot_ShowAll()
 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    Dim strDateFormat As String
 
    Application.ScreenUpdating = False
     
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        [COLOR="Red"]pt.PivotCache.Refresh[/COLOR]
        For Each pf In pt.PivotFields
            If pf.DataType = xlDate Then
                strDateFormat = pf.NumberFormat
                pf.NumberFormat = "0"
            End If
            [COLOR="Red"]On Error Resume Next[/COLOR]
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
            [COLOR="Red"]On Error GoTo 0[/COLOR]
            If pf.DataType = xlDate Then pf.NumberFormat = strDateFormat
        Next pf
        pt.ManualUpdate = False
    Next pt
 
    Application.ScreenUpdating = True
    
    Exit Sub
 
End Sub

 

[mick0005]

This is what I think is happening but it is a guess.

You created the Pivot Table when one of the Active Indicator formulas had a Previous LOS as a result. You then changed the data and now there is no Previous LOS result in the Active Indicator column. You then refreshed the Pivot Table data.

In the refreshed pivot table, there is still a Previous LOS item in the Active Indicator field, but there is no Previous LOS data to actually select. This is what's causing the error when trying to reset the Previous LOS item to .Visible = True.

If this is actually the case, I think the simplest way around it is to just ignore the error.

Sub Pivot_ShowAll()
 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    Dim strDateFormat As String
 
    Application.ScreenUpdating = False
     
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        [COLOR=Red]pt.PivotCache.Refresh[/COLOR]
        For Each pf In pt.PivotFields
            If pf.DataType = xlDate Then
                strDateFormat = pf.NumberFormat
                pf.NumberFormat = "0"
            End If
            [COLOR=Red]On Error Resume Next[/COLOR]
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
            [COLOR=Red]On Error GoTo 0[/COLOR]
            If pf.DataType = xlDate Then pf.NumberFormat = strDateFormat
        Next pf
        pt.ManualUpdate = False
    Next pt
 
    Application.ScreenUpdating = True
    
    Exit Sub
 
End Sub

I tired using your replacement code. This time there are obviously no errors, but when I run it, nothing happens. It doesn't unfilter any fields or anything.

The source data does change frequently. Statuses may very well change based on revisions that we make to the start and end dates (as these dates feed the if(and formula used in the Active Indicator cells.

Additionally, I am using a bit of code on the pivot table worksheet to auto-refresh the pivot table when the sheet becomes active. I didn't think of that before but wonder if it could have any impact.

Here is the code:

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
     'Change "Pivot" to your sheet's name
     'Change "PivotTable1" to your pivot table's name
     
    Sheets("Report").PivotTables("PivotTable2").RefreshTable
End Sub

 

[BritGeek]

Hi,

This might be overly simple, but if you're looking to clear the filters; would this not solve things?


Sheets("Sheet Name").PivotTables("Pivot Table Name").ClearAllFilters


You'd have to update the sheet (or use ActiveSheet.Name) and pivot-table name.

Good luck.

- BritGeek

 

[MacGyver7640]

Hi,

This might be overly simple, but if you're looking to clear the filters; would this not solve things?


Sheets("Sheet Name").PivotTables("Pivot Table Name").ClearAllFilters


You'd have to update the sheet (or use ActiveSheet.Name) and pivot-table name.

Good luck.

- BritGeek

BritGeek, I was browsing for the solution to the subject header of this post. This is exactly what I was looking for.

AlphaFrog's code worked too, but was very slow. Maybe it was overcoming a problem I wasn't having.

Thank you!