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]

Sub Pivot_ShowAll()

    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    
    Application.ScreenUpdating = False
    
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
        Next pf
        pt.ManualUpdate = False
    Next pt
    
    Application.ScreenUpdating = True
    
End Sub

 

[mick0005]

Sub Pivot_ShowAll()

    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    
    Application.ScreenUpdating = False
    
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
        Next pf
        pt.ManualUpdate = False
    Next pt
    
    Application.ScreenUpdating = True
    
End Sub

Thanks so much for the help. I am encountering an error when trying to use it. I have posted the screenie of the error below.

 

[Richard Schollar]

Do you have any date fields in your PivotItems?

 

[mick0005]

I have one data field, used as a count for verification purposes with the data we're using. The field is actually titled "data".

 

[mick0005]

Do you have any date fields in your PivotItems?

Here is a screenshot of the fields I am using and layout.

 

[Richard Schollar]

It was DATE fields I was interested in (not DATA fields)

There's a bug in Excel which means you can't access or amend the Visible property for Row Fields (possibly Column fields) which are of Data datatype (and Date formatted).

To get around this (and get AlphaFrog's code to work) you could temporarily convert the date fields of your Pivot Table (ie Start Date and End Date to a number format) and then run Alpha's code then change them back to dates afterwards.

eg try this:

Sub Pivot_ShowAll()
 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
 
    Application.ScreenUpdating = False
 
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        'On Error Resume Next   '==== reqd if more than one pivot table in sheet and does not have Start and End Date fields
        pt.PivotFields("Start Date").NumberFormat = "0"
        pt.PivotFields("End Date").NumberFormat = "0"
        'On Error Goto 0    '====reqd as above
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
        Next pf
        'On Error Resume Next   '=== Ditto
        pt.PivotFields("Start Date").NumberFormat = "dd/mm/yyyy"
        pt.PivotFields("End Date").NumberFormat = "dd/mm/yyyy"
        'On Error Goto 0   '=== Ditto
        pt.ManualUpdate = False
    Next pt
 
    Application.ScreenUpdating = True
 
End Sub

 

[mick0005]

It was DATE fields I was interested in (not DATA fields)

There's a bug in Excel which means you can't access or amend the Visible property for Row Fields (possibly Column fields) which are of Data datatype (and Date formatted).

To get around this (and get AlphaFrog's code to work) you could temporarily convert the date fields of your Pivot Table (ie Start Date and End Date to a number format) and then run Alpha's code then change them back to dates afterwards.

eg try this:

Sub Pivot_ShowAll()
 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
 
    Application.ScreenUpdating = False
 
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        'On Error Resume Next   '==== reqd if more than one pivot table in sheet and does not have Start and End Date fields
        pt.PivotFields("Start Date").NumberFormat = "0"
        pt.PivotFields("End Date").NumberFormat = "0"
        'On Error Goto 0    '====reqd as above
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
        Next pf
        'On Error Resume Next   '=== Ditto
        pt.PivotFields("Start Date").NumberFormat = "dd/mm/yyyy"
        pt.PivotFields("End Date").NumberFormat = "dd/mm/yyyy"
        'On Error Goto 0   '=== Ditto
        pt.ManualUpdate = False
    Next pt
 
    Application.ScreenUpdating = True
 
End Sub

Sorry I read that as DATA.

I tried the new code and I got the below error, then ran debug and have posted both screenshots.

 

[AlphaFrog]

I wasn't aware of the "bug" that Richard pointed out.

Maybe try something like this.

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
        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
 
End Sub

 

[mick0005]

I wasn't aware of the "bug" that Richard pointed out.

Maybe try something like this.

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
        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
 
End Sub

AlphaFrog - Thanks again for the attempt. I have posted the error I received and debug when I tried running with your new code. Any ideas?