Free Microsoft Excel 2013 Quick Reference

Reset pivot table filters back to (ALL)

Is there a macro that can do this? I have searched high and low, to no avail.

There is a chart (it is NOT a pivot chart!) above the pivot table in A1:L24. Not sure if this would be an impediment to running the macro, or not.

BTW, the pivot table is named "PivotTable3".

There are 4 drop down filter criteria in the pivot table. I need a create a button (using autoshapes) that will be assigned this macro, and when it is depressed, will reset all the filters back to default condition "ALL".

Thanks for any help...


Post your answer or comment

comments powered by Disqus
I need a macro to reset pivot table filters back to "ALL" that I can assign to a button.
I have about 10 fixed filters in the columns A&B that need to be selected and reapplied everytime a new user wants to apply a new filterset.

Ideas?..

Thanks,
G.

So I have a workbook containing this event to auto refresh my pivot table whenever any one of the 10 or so pivot table filters are applied:

Private Sub Worksheet_Calculate()

'If data on this worksheet changes, refresh the pivot table
Sheets("SA").PivotTables("PivotTable1").RefreshTable

End Sub

It works great.

My problem is that I want to install a pivot table reset macro (button) that auto resets all pivot filters to default (ALL). But if I were to do that, the pivot table autorefresh event script will kick in, as my pivot table tries to painfully refresh itself as each filter is being reset back to a the default condition (ALL).

How can I write one macro that leaves the pivot table refresh event functioning EXCEPT when I press a global pivot table filter reset button to (ALL)?

Thanks~!

hi,

i have a pivot table, and would like create a button where when pressed, it will run a specified set of search filters across the 10 odd columns I have.

i also need a button that when pressed, makes the pivot table 'show all' in all its fields again, so I don't have to manually go back and tick 'show all' in each column. I found some macro scripts on the net but couldn't get them to work properly, excel would just spaz out, maybe because theres 4000 odd rows to search through? I don't know.

But anyway, would really like 2 macro's to run this. My pivot table has 4000 rows, and 10 columns with the pivot table filters. So to summarise - would like a button that when pressed, automatically ticks tick boxes to search for criteria, and another button that resets everything again.

can this be done?

1. I need a macro to reset all the filters of pivot table back to default "ALL" selection.

Background: I have about 10 pivot filters in the columns A&B that need to be selected and reapplied everytime a new user wants to apply a new filterset. I need a macro that resets them back to "ALL" or "Multiple Selections" setting.

2. I already have a macro installed in the same workbook that refreshes this same pivot table everytime any of the filters are selected and changed by the spreadsheet user (example: from "All" to seleection "Under" or, from "All" to selection "Over").

Background: (Here is that pivot table refresh macro):

Private Sub Worksheet_Calculate()

'If data on this worksheet changes, refresh the pivot table
Sheets("Southwest").PivotTables("PivotTable1").RefreshTable

End Sub

Unfortunately, even if I installed another macro that some member may create as a solutiion to my #1 problem listed above (i.e., a macro to reset the pivot table filters) it would probably be 'overidden' by this reset pivot table filters macro.

I guess the bottom line is I need a macro that can somehow reset all the Pivot table filters back to "All", without invoking the Pivot Table Refresh macro shwon above.

Hope someone can help.

Hello experts,
I've reviewed PT0021 Change All Page Fields sample Excel file (for 2007) on
http://www.contextures.com/excelfiles.html#Pivot and am still having trouble
with my complex workbook with several pivot tables/charts. I have found in
this sample file that it works when you choose an explicit value in the
filter, however when you re-set the filters back to 'all' or choose multiple
values, this does not apply to subsequent pivot tables and worksheets. Is
this only possible with explicit values in the main filter or can the code be
modified to work when choosing 'all'?

Also, my workbook contains several worksheets of data, and subsequent
worksheets of various pivot tables for each set of data. The field names
would be consistent across each worksheet of data, however in my ideal world,
I'd like one main set of filters for a main pivot table to control ALL pivot
tables on subsequent worksheets, which feed from a variety of data worksheets
(all in the same workbook). Am I dreaming? So far, Excel pros that I've
solicited help from using the sample file referenced above have not been able
to make this work. Thank you.

Hello,
I have a pivot table with 7 fields in the Report Filter section. I am trying to create a macro so once someone is done with their filtering they can click the macro button and the pivot table will refresh to all.

This one from a different pivot but it works.
Sub HireYear()
'
' HireYear Macro
'
' Keyboard Shortcut: Ctrl+s
'
Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Grade @ Hire"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Grade @ Hire")
        .PivotItems("10").Visible = True
        .PivotItems("11").Visible = True
        .PivotItems("13").Visible = True
    End With
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Grade @ Hire"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Grade @ Hire")
        .PivotItems("10").Visible = True
        .PivotItems("11").Visible = True
        .PivotItems("13").Visible = True
    End With
End Sub
So I used it as a template for my larger pivot table with multiple fields in the ReportFilter section. Below is what I've done, however when I run the macro it is still leaving some of the fields filters instead of reseting back to all. Can you help? Thank you

Sub LengthofService()
'
' LengthofService Macro
'
' Keyboard Shortcut: Ctrl+d
'
Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Source"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Source")
        .PivotItems("College-AS Tgt").Visible = True
        .PivotItems("College-Corp Tgt").Visible = True
        .PivotItems("College-Non Tgt").Visible = True
        .PivotItems("External Liberty Website").Visible = True
        .PivotItems("Intern Conversion").Visible = True
        .PivotItems("Internal").Visible = True
        .PivotItems("Referral").Visible = True
        .PivotItems("Rehire").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Grade @ Hire"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Grade @ Hire")
        .PivotItems("10").Visible = True
        .PivotItems("11").Visible = True
        .PivotItems("13").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Meets College GPA"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Meets College GPA")
        .PivotItems("Yes").Visible = True
        .PivotItems("No").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Major Cat."). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Major Cat.")
        .PivotItems("Accounting").Visible = True
        .PivotItems("Business").Visible = True
        .PivotItems("n/a").Visible = True
        .PivotItems("Other Non-Bus").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Inclusion"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Inclusion")
        .PivotItems("Y").Visible = True
        .PivotItems("N").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Reason for Term"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Reason for Term")
        .PivotItems("Displeasure with Management").Visible = True
        .PivotItems("Internal Transfer").Visible = True
        .PivotItems("Job Fit").Visible = True
        .PivotItems("Limited Advancement").Visible = True
        .PivotItems("N/A").Visible = True
        .PivotItems("Pay").Visible = True
        .PivotItems("Performance").Visible = True
        .PivotItems("Personal").Visible = True
        .PivotItems("Poor Job Fit").Visible = True
        .PivotItems("Retirement").Visible = True
        .PivotItems("Return to School").Visible = True
        .PivotItems("RIF").Visible = True
        .PivotItems("Transfer").Visible = True
        .PivotItems("Travel/Isolation").Visible = True
        .PivotItems("Workload").Visible = True
        .PivotItems("(blank)").Visible = True
    End With
End Sub


Hi all,

I have a pivot table which reads from a fairly large list of entries (about 17000 records). The table was tedious to build, so I typically update the data, then refesh the table to produce the new results. The issue I have is that the pivot table field filter dropdown boxes tend to accumulate obsolete data over time and I cannot figure out how to reset these so that only the current values are shown.

For instance, I have a field "Prog" which lists various programs in progress. When I created the table, Prog1, Prog2, and Prog3 existed, and the pivot filter dropdown listed all three. Over time, some of these programs completed, and the records were deleted so data no longer include Prog1 and Prog2. New programs Prog4 and Prog5 were also added. However, the field filter list box shows all 5. (While this is not a big deal with 5 projects, it is wihen the list climbs to 50.)

If anyone can tell me how to reset this without completely rebuilding the pivot table, I'd appreciate it. I'm using Excel 2003 SP3 and Windows XP.

---GJC

I want to create multiple pivot tables each performing its own tasks. When i want to filter a particular category in all first pivot tables i have to do this one by one. This is time consuming and i think it can be done faster. Is it possible when i filter a category in pivot table 1 that this filter is automatically filtered in the other pivot tables?

Hi all,
Could anyone plz tell me, How to filter one of the Pivot Table Combobox, according to selection of another Pivot Table Combobox?

For more info, see the attachment...

I'm new to excel macro and new to this forum, and I have found a lot of helpful material here already.

I need a macro that will automatically change a table's autofilter to the same selections/values of the pivot table filter when the pivot table's pivot filter is changed.

For Exaple:
I have a pivot table with a pivot filter Date in B3 with value 1/1/2011, 2/1/2011, and 3/1/2011

I have a table(at A18) with a autofilter Date with the same value 1/1/2011.2/1/2011. 3/1/2011.

What I would like to do is when I change the pivot filter in B3 (for example, select 1/1/2011 and 2/1/2011), the table autofilter will be set to 1/1/2011 and 2/1/2011.

I would prefer to have two-way capabilities, but I can work with only one-way change.

This is the code I came up with so far, it only works with single selections, but I would need multiple selection capabilities. I'm using Excel 2007


	VB:
	
 test2() 
    Dim KEY As Variant: KEY = Sheets("Yesterday").Range("B3").Value 
    Select Case KEY 
    Case Is = "(All)" 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=4 
    Case Else 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=4, Criteria1:=Sheets("Yesterday").Range("B3").Value 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you in advance for any help and insight!

Hi,

I have been looking around at various threads etc to try and update this, but to no avail. So i have signed up and hopefully someone will be able to help this..

I have a lot of data and am trying to write a macro to automaticall update a pivot table based on a specific cell value.
I have attached a sample excel file. I would like it when a project code is entered into cell B2 the pivot table updates automatically to this value.
I have taken a selection of this data for this purpose, but I have over 400 project codes - so when I record the macro it lists all projects as

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project")
.PivotItems("RD09196").Visible = True
.PivotItems("RD10086").Visible = False
etc, etc

End With

This isnt really possible with the amount of codes I have as it takes FOREVER!

I need to make the pivot table clear all current filter selections so only the one value in the cell is shown in the pivot.

Any help is greatly appreciated!

Thanks
Rob

I'm new to excel macro and new to this forum, and I have found a lot of helpful material here already.

I need a macro that will automatically change a table's autofilter to the same selections/values of the pivot table filter when the pivot table's pivot filter is changed.

For Exaple:
I have a pivot table with a pivot filter Date in B3 with value 1/1/2011, 2/1/2011, and 3/1/2011

I have a table(at A18) with a autofilter Date with the same value 1/1/2011.2/1/2011. 3/1/2011.

What I would like to do is when I change the pivot filter in B3 (for example, select 1/1/2011 and 2/1/2011), the table autofilter will be set to 1/1/2011 and 2/1/2011.

I would prefer to have two-way capabilities, but I can work with only one-way change.

This is the code I came up with so far, it only works with single selections, but I would need multiple selection capabilities. I'm using Excel 2007

Sub test2()
Dim KEY As Variant: KEY = Sheets("Yesterday").Range("B3").Value
Select Case KEY
Case Is = "(All)"
Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1
Sheets("Yesterday").Range("A18").AutoFilter Field:=4
Case Else
Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1
Sheets("Yesterday").Range("A18").AutoFilter Field:=4, Criteria1:=Sheets("Yesterday").Range("B3").Value
End Select
End Sub

Thank you in advance for any help and insight!

I consider myself a power user of pivot tables, but recently, this one has stumped me. For some reason every time I create a pivot table now, all the autoformatting options do NOT have borders in them AND is turning all cells color white; so I have to manually change the borders for each report. The autoformatting for non-Pivot Tables works fine still and I cannot find any option or setting that I would have changed to result in this. Any ideas how to fix this or return the Pivot Table autoformatting back to normal?

Hi All,

I am using excel 2007 and am trying to apply some filters to some pivot tables that were created in 2003.

However when I try to filter the fields, the options are greyed out (see image), I can still use a top 10 filter, or manually go through and untick the options that I don't want to see but obviously this isn't ideal. My colleague using 2003 who made these pivot tables can use these filters just fine.

excel 2007 pivot table filter.JPG

If I convert the file to the new format these options do become available, but then the pivot isn't compatible with 2003. Is there anyway to get these filters working?

Hi - I have question / request regarding Pivot Tables that I can't seem to solve:

Scenario: I have a worksheet full of ~360 tasks. Tasks are broken down into phases, have owners, and have status'. I leverage a pivot table to easily sort my data by owner, or by status. When reviewing the pivot table with others we want to update the raw data. This entails changing worksheets and scrolling to the task in question.

I'm looking for an easier way to update the raw / original data.

Options I've tried to pursue but have come up empty:
Option 1: Excel modification? Is there a setting in Excel 2007 that would allow me to change a value in my pivot table and have it propagate to the original data set (another Excel worksheet)? So far the answer seems to be "no". Is this possible?

Option 2: Hyperlink from Pivot Table to Raw data field.
- I cannot create hyperlinks within PivotTable data cells.
- I have used the "hyperlink()" function OUTSIDE of my pivot table, along w/ a "match()" on a key field in my pivot table to create hyperlinks back to my raw data... But this is prone to breaking.
- Is there a way to enable hyperlinks from within a Pivot Table? I have seen other requests from individuals that had a pivot table with actual hyperlinks (e.g. http://www.x.com or user@email.com) and they could not 'activate' them.

Option 3: Tool Tip VBA Form w/ Link?
- If I can't create hyperlinks directly in the table, then how about a function so then when i click (mouse over?) a pivot table cell a form appears with a hyperlink back to the source data?

Option 2 and 3 Assume that there's an way to get at the underlying location of the raw data (e.g. "Pivot Table Worksheet!E10" comes from "RawData!C4". I've dug through some VBA documentation at Microsoft.com but came up empty. I know this link must exist behind the scenes - In Excel 2007 when you mouse-over a pivot table cell Excel displays: "Row - Phase - Owner - Status", which is directly out of the raw data table/worksheet. [[This was an incorrect assumption... When i looked at the pivot table tool-tip closer I realize it was just walking through my data :'( row. (I guess if you had a really long list this would be helpful)]]

What is the VBA Code to revel the source cell? If i can get at that data, (and hyperlinking can be turned on w/in the pivot table...) then I can easily write a script to walk through the pivot table and create links back to the raw data... Not a perfect solution - but it takes away the need to search through the source data for the row I want.

Any thoughts / guidance on any of these options to solve my overall issue would be greatly appreciated.

Thanks!
Dave

*Updated Option 2/3 - corrected my assumption.

I am using pivot table to generate sales forecast from my source data. Is
there a way I can directly jump back to the source data to edit them? If I
double click the value in pivot table I only get a detail on a separate new
sheet with respective records. Can I include any form of hyperlink or macro
action into the pivot table to manage the link? Thanks.

Hi Guys,

I've been looking around for the answer and found partial answer here:

Forum > Microsoft Office Application Help - Excel Help forum > Excel Programming > Change Pivot Table Filter Based on Cell Value.

The answer posted by T-J was:

Sub Apply_Date_Filter_From_Worksheet()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterDate As Date
    
    Set pvtTable = Worksheets("Pivot_Sheet").PivotTables("PivotTable1")
    Set pvtField = pvtTable.PivotFields("Date")

    filterDate = CDate(Worksheets("Controls_Sheet").Range("B2"))
    
    On Error Resume Next  'in case date not found
    
    
        For Each pvtItem In pvtField.PivotItems
            If CDate(pvtItem.Value) = filterDate Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If
        Next pvtItem
       
    
    
End Sub
I can adapt this to my workbook & it works okay, but I need to change the filter to a number of different values in a list, some of which might not be available in the data.

Background :

I'm taking 14,000 entries of Incident data & pivoting the incidents based on where they were resolved, the filter will be based on which resolution group has been given a 'tag' of Onsite/Remote/Customer, these would each have their own tab creating the list i'd like to pivot from.

I need the pivot to display all incidents resolved by resolution groups 'tagged' as onsite (I.e. all those groups in list one on Sheet 'Onsite')

These may not all be present in the incident data, i dont know if that will cause errors if it attmempts to set the filter to something that isnt there...

I need to make the following code only apply to pivot tables in a specified worksheet. For example, changing the data validation is cell A3 will only update the pivot tables in sheet1

Private Sub Worksheet_Change(ByVal Target
As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "IP1"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

    If Target.Address = Range("A3").Address Then
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Hello everyone !

I just started learning VBA (i'm a newbie !) and its relatively an interesting language to study on . Currently I'm working on pivot tables using excel macro and I really need your help in this .

Basically , I need to filter out the year submitted according to the current year . I did this to filter out my pivot :

=======================================================

For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
PivItem.Visible = True
Next PivItem

For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
Select Case PivItem.Name
Case "2012"
PivItem.Visible = True
Case Else
PivItem.Visible = False
End Select
Next PivItem

=======================================================

But this can only filter out year 2012 . I need to use this workbook for the next couple of years and I dont want to keep modifying the codes . So please help me with this :/ Thankyou so very much <3

Regards ,
Sheahhh

Hello all, I'm getting a bit frustrated as I'm no VBA ninja. I'm trying to do something that feels like it should be really simple but I'm finding it quite tough.

I'm using Excel 2007 and have a .xlsm workbook that contains several pivot tables on different worksheets. Each pivot table links to the same access database and just displays the results of queries to make an easy to print report.

The underlying data has a field which lists organisation names and I would like to be able to run some code which would loop round each pivot table in the workbook and filter each one to display information relevant to each organisation.

Ultimately I also want the code to print the report after it's done this but one step at a time, for now I just want to get the filtering side of things working.

Here's how I want it to work eventually:

1: Filter pivot table1 to display Organisation1 --> Filter pivot table2 to display Organisation1 --> Print
2: Filter pivot table1 to display Organisation2 --> Filter pivot table2 to display Organisation2 --> Print
3: Filter pivot table1 to display Organisation3 --> Filter pivot table2 to display Organisation3 --> Print

So, here is where I am thus far (sans printing, just want the nuts and bolts to function right now):

Sub
Filter()

Dim Worksheet
Dim PivotTable

'loop 1 works through all worksheets
For Each Worksheet In Worksheets

'not sure I need this, can I instead change ActiveSheet.PivotTables to Worksheet.PivotTables later on in loop2?
Worksheet.Select

'loop 2 works through all pivot tables in each worksheet
For Each PivotTable In ActiveSheet.PivotTables

  'this bit only here to display details of code progress for my benefit
  MsgBox PivotTable.Name & "/" & Worksheet.Name & "/" & ActiveSheet.Name
  
  'alter page item filter, this is the bit that seems to go *very* wrong
  PivotTable.PivotFields("ORG_NAME").CurrentPage = "Organisation1"

'close loop 1
Next PivotTable

'close loop 2
Next Worksheet

End Sub
If I comment out the row that sets the filtering up then the code seems to work fine (based on the text seen in the message box).

I have seen other examples where a bit of code along the lines of 'i = 1 to Worsheets.count' in order to generate the loop, not sure which method is better but the main problem seems to be around setting the pivot table filter. I have also tried using the following to no avail:

PivotTable.PivotFields("ORG_NAME").CurrentPage = _
"Organisation1"
I hope I'm not making a completely amateurish mistake but suspect I might be.

Almost forgot, the actual error message I get is "Error '1004': Unable to get the PivotFields property of the PivotTable class".

Thanks all

Si

Dear all Excel experts,

i have a small issue that i hope you can help me solve. i have a pivot table, and want to choose multiple filters for it. The task is simple, BUT i would like the table to only refresh AFTER i have chosen ALL the filters, and not like it is set in standard - after choosing each filter.

is there any way to do this? i would expect that a macro should be able to handle that, but i have no experience in programming.

hope you can help!

Is it possible to have a combo or list box control filters on multiple pivot tables and allow for more than one field to be selected?

For example, i have a month filter on several different pivot tables. I want to change just one filter, and have the rest of the month filters change to match my selection. But i also want to be able to select more than one month at a time.

I've attached an example file.

There's 7 pivot tables on one sheet (pivottable1-7), and i want one master month filter that will control the month filter on all of the pivot tables.

I'm at a loss on how to accomplish this, and any insight would be greatly appreciated.

Can anyone help with code that would reset all pivot table row fields to All. I have the following code but it takes over 10 minutes to run - I need it to run much faster than that. Note I have it limitted to reset only the first 16 row fields.


	VB:
	
 ResetPivotItems() 
     
    Application.ScreenUpdating = False 
     
    Dim pt As PivotTable 
    Dim Pf As PivotField 
    Dim Pi As PivotItem 
    Dim x As Integer 
     
    Set pt = ActiveSheet.PivotTables("PivotTable1") 
     
    For Each Pf In pt.PivotField 
        x = x + 1 
        For Each Pi In Pf.PivotItems 
             
            Pi.Visible = True 
             
        Next Pi 
        If x > 17 Then 
            Exit For 
        End If 
    Next Pf 
     
    Application.ScreenUpdating = True 
     
    MsgBox "Done" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


When I refresh my pivot table against new data, if there are new entries in the filtered field, the filter no longer works.

For example: I have 10 rows with data on dogs. One of the column headings is 'Breed'. Initially this column contains 1 of the following: beagle, poodle or mutt. My pivot table filters to select only rows with Breed = beagle.

As new data is added, as long as the 'Breed' is beagle, poodle or mutt, the filter for 'beagle' works fine.

But when a record is added with a new entry for 'Breed' - say 'Cocker Spaniel' - this new entry is now included in the pivot data. In other words, the filter on 'Breed' now includes both 'beagle' and 'Cocker Spaniel'.

What am I missing???

thanks


No luck finding an answer? You could always try Google.