VBA: forcing page filter on pivot table

D

dhregan

New Member
Joined
Mar 30, 2010
Messages
37
Hello -

I have a pivot table which includes a page filter using a data field with possible values of "Yes", "No" or blank. The initial setting for the page filter is "Yes". However, there are some situations when the data field is updated to "No" for all rows. When this happens, the page filter flips to "(All)" when the pivot table is refreshed in VBA. Is there a way to force the page filter to remain as "Yes"? I need the result to contain zero values in the pivot table for this use case.

Thanks in advance.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi Dhregan,

You can use VBA e.g., construct a macro to refresh to pivot table and call a second macro that resets the filter.

For instance: in a module, (hit: alt F11 and in the vba editor; select from the Insert drop-down: "module" ). Paste the below code into the module window, taking care to substitute the example pivot table name (YourPivotTableName) with the name of your actual Pivot Table!

VBA Code: 
Sub RefreshPivotMacro()

Dim pt As PivotTable

    Set pt = ActiveSheet.PivotTables("YourPivotTableName")
    pt.RefreshTable

Call ResetFilter

End Sub

In order to construct the ResetFilter macro, you need to consider the question of: whether 'Your Field Name' is in the Report Filter or in the Column or Row Labels.
If 'Your Field Name' is in the Report Filter, add the below macro beneath your RefreshPivotMacro:

VBA Code: 
 Sub ResetFilter()
   Application.ScreenUpdating = False
   ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = True

   ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").ClearAllFilters

   ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").CurrentPage = _
      "Yes"

  ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

If 'Your Data Field' is in the Column or Row Labels, instead add the below macro beneath your RefreshPivotMacro:

VBA Code: 
 Sub FilterPivotTable()
     Application.ScreenUpdating = False
     ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = True

      ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").ClearAllFilters

      ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:="Yes"

  ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

Then you can either create a form control button on your Pivot Table Worksheet (ws) e.g., in the Developer Tab, go to >>Insert-->Form Controls-->>Button (Form Control). You then right-click the form control button and "Assign Macro" e.g., select: RefreshPivotMacro.

Alternatively, you can set the pivot table to automatically refresh: by pasting the RefreshPivotMacro into the Worksheet window instead of the module window i.e., in VBA editor e.g., paste into Sheet2 (PivotTable) instead of Module 1.
If you do this, you have to modify the macro to:

VBA Code: 
Private Sub Worksheet_Activate()

Dim pt As PivotTable

    Set pt = ActiveSheet.PivotTables("YourPivotTableName")
    pt.RefreshTable

Call ResetFilter

End Sub
 
Upvote 0
dougmarkham said:
Hi Dhregan,

You can use VBA e.g., construct a macro to refresh to pivot table and call a second macro that resets the filter.

For instance: in a module, (hit: alt F11 and in the vba editor; select from the Insert drop-down: "module" ). Paste the below code into the module window, taking care to substitute the example pivot table name (YourPivotTableName) with the name of your actual Pivot Table!

VBA Code: 
Sub RefreshPivotMacro()

Dim pt As PivotTable

    Set pt = ActiveSheet.PivotTables("YourPivotTableName")
    pt.RefreshTable

Call ResetFilter

End Sub

In order to construct the ResetFilter macro, you need to consider the question of: whether 'Your Field Name' is in the Report Filter or in the Column or Row Labels.
If 'Your Field Name' is in the Report Filter, add the below macro beneath your RefreshPivotMacro:

VBA Code: 
Sub ResetFilter()
   Application.ScreenUpdating = False
   ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = True

   ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").ClearAllFilters

   ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").CurrentPage = _
      "Yes"

  ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

If 'Your Data Field' is in the Column or Row Labels, instead add the below macro beneath your RefreshPivotMacro:

VBA Code: 
Sub FilterPivotTable()
     Application.ScreenUpdating = False
     ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = True

      ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").ClearAllFilters

      ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:="Yes"

  ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

Then you can either create a form control button on your Pivot Table Worksheet (ws) e.g., in the Developer Tab, go to >>Insert-->Form Controls-->>Button (Form Control). You then right-click the form control button and "Assign Macro" e.g., select: RefreshPivotMacro.

Alternatively, you can set the pivot table to automatically refresh: by pasting the RefreshPivotMacro into the Worksheet window instead of the module window i.e., in VBA editor e.g., paste into Sheet2 (PivotTable) instead of Module 1.
If you do this, you have to modify the macro to:

VBA Code: 
Private Sub Worksheet_Activate()

Dim pt As PivotTable

    Set pt = ActiveSheet.PivotTables("YourPivotTableName")
    pt.RefreshTable

Call ResetFilter

End Sub
Click to expand...

Hello Doug -

Thank you for your response. I verified that "Your Data Field" is indeed a Report Filter, so I tried inserting the corresponding code that you provided. However, when I run it, I get a "1004 Unable to get the PivotFields property of the PivotTable class" error. I confirmed that the worksheet and workbook are unprotected, but still get the error. Any ideas?

Thanks,
David
 
Upvote 0
dhregan said:
Hello Doug -

Thank you for your response. I verified that "Your Data Field" is indeed a Report Filter, so I tried inserting the corresponding code that you provided. However, when I run it, I get a "1004 Unable to get the PivotFields property of the PivotTable class" error. I confirmed that the worksheet and workbook are unprotected, but still get the error. Any ideas?

Thanks,
David
Click to expand...

Hi David,

When you get the Run-time error 1004, please select "Debug". The VB Editor should open and a line of Code should be highlighted in yellow. Please reply to this post by pasting the yellow code between VBA tags.

Kind regards,

Doug.

P.S. if it's not business sensitive, you can always post your wb on Google Drive and send a link to allow quicker wb specific diagnosis.
 
Upvote 0
Hello Doug -

Here is the code block that I have rewritten:

VBA Code: 
' Refresh pivot table on DART_INT_PV tab
        ActiveWorkbook.Unprotect Password:=Template_PW
        Sheets("DART_INT_PV").Unprotect Password:=Template_PW
        Set DART_PIVOT_TABLE = Sheets("DART_INT_PV").PivotTables("DART_ERRORS_PIVOT_TABLE")
        DART_PIVOT_TABLE.ManualUpdate = True
        DART_PIVOT_TABLE.PivotFields("Follow_Up").ClearAllFilters
        DART_PIVOT_TABLE.PivotFields("Follow_Up").CurrentPage = "Yes"
        DART_PIVOT_TABLE.ManualUpdate = False
        DART_PIVOT_TABLE.RefreshTable
        ActiveWorkbook.Protect Password:=Template_PW, Structure:=True
        Sheets("DART_INT_PV").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True

The 1004 error occurs on the DART_PIVOT_TABLE.PivotFields("Follow_Up").ClearAllFilters line.
 
Upvote 0
dhregan said:
The 1004 error occurs on the DART_PIVOT_TABLE.PivotFields("Follow_Up").ClearAllFilters line.
Click to expand...

Hi David,

I think if you're setting a pivot table, you'll need to define the object DART_PIVOT_TABLE as a pivot table e.g.,

VBA Code: 
        Dim DART_PIVOT_TABLE As PivotTable

        ' Refresh pivot table on DART_INT_PV tab
        ActiveWorkbook.Unprotect Password:=Template_PW
        Sheets("DART_INT_PV").Unprotect Password:=Template_PW
        
        Set DART_PIVOT_TABLE = Sheets("DART_INT_PV").PivotTables("DART_ERRORS_PIVOT_TABLE")
        DART_PIVOT_TABLE.ManualUpdate = True
        DART_PIVOT_TABLE.PivotFields("Follow_Up").ClearAllFilters
        DART_PIVOT_TABLE.PivotFields("Follow_Up").CurrentPage = "Yes"
        DART_PIVOT_TABLE.ManualUpdate = False
        DART_PIVOT_TABLE.RefreshTable

        ActiveWorkbook.Protect Password:=Template_PW, Structure:=True
        Sheets("DART_INT_PV").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True

Kind regards,

Doug
 
Upvote 0
dougmarkham said:
Hi David,

I think if you're setting a pivot table, you'll need to define the object DART_PIVOT_TABLE as a pivot table e.g.,

VBA Code: 
        Dim DART_PIVOT_TABLE As PivotTable

        ' Refresh pivot table on DART_INT_PV tab
        ActiveWorkbook.Unprotect Password:=Template_PW
        Sheets("DART_INT_PV").Unprotect Password:=Template_PW
       
        Set DART_PIVOT_TABLE = Sheets("DART_INT_PV").PivotTables("DART_ERRORS_PIVOT_TABLE")
        DART_PIVOT_TABLE.ManualUpdate = True
        DART_PIVOT_TABLE.PivotFields("Follow_Up").ClearAllFilters
        DART_PIVOT_TABLE.PivotFields("Follow_Up").CurrentPage = "Yes"
        DART_PIVOT_TABLE.ManualUpdate = False
        DART_PIVOT_TABLE.RefreshTable

        ActiveWorkbook.Protect Password:=Template_PW, Structure:=True
        Sheets("DART_INT_PV").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True

Kind regards,

Doug
Click to expand...
Hello Doug -

Yup - This was already declared as a pivot table, so I don't think that is the issue. Any other suggestions?

Regards,
David
 
Upvote 0
You have a field with this name: "Follow_Up", check that it is well written, also that there are no blanks before or after the name.
 
Upvote 0
Thank you Dante. Yes, the field name is correct. My pivot table works correctly when there are results in the Follow_Up column with "Yes" and "No" values. The issue is that when there are no "Yes" values, the pivot table reverts to "(All)" which provides incorrect counts since it picks up the "No" values and does not report all zeroes for the "Yes" result. Is there a way to force the "Yes" count in the report filter within the pivot table even when there are no "Yes" results?
 
Upvote 0
So you have the problem in this line:

DART_PIVOT_TABLE.PivotFields("Follow_Up").CurrentPage = "Yes"

Check if the following works for you:

VBA Code: 
Sub test2()
  Dim DART_PIVOT_TABLE As PivotTable, itm As PivotItem, st As Boolean
  
  'ActiveWorkbook.Unprotect Password:=Template_PW
  'Sheets("DART_INT_PV").Unprotect Password:=Template_PW
  Set DART_PIVOT_TABLE = Sheets("DART_INT_PV").PivotTables("DART_ERRORS_PIVOT_TABLE")
  
  With DART_PIVOT_TABLE
    .PivotCache.MissingItemsLimit = xlMissingItemsNone
    .ManualUpdate = True
    .PivotFields("Follow_Up").ClearAllFilters
    For Each itm In .PivotFields("Follow_Up").PivotItems
      If itm = "Yes" Then
        st = True
        Exit For
      End If
    Next
    If st = True Then
      .PivotFields("Follow_Up").CurrentPage = "Yes"
    Else
      MsgBox "there are no 'Yes' values"
    End If
    
    .ManualUpdate = False
    .RefreshTable
  End With
  
  'ActiveWorkbook.Protect Password:=Template_PW, Structure:=True
  'Sheets("DART_INT_PV").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Filter Pivot Table Generated From PowerQuery Data Model
Replies
1
Views
125
jdorby
J
M
  • Question
VBA Macro with option to filter Pivot Tables multiple times.
Replies
0
Views
36
Macro_nerd
M
gravanoc
  • Solved
Active filter on pivot prevents new data source items from appearing
Replies
2
Views
53
gravanoc
gravanoc
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A
F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
233
Domenic
D

Forum statistics

Threads
1,215,516
Messages
6,125,286
Members
449,218
Latest member
Excel Master

Share this page

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