Can't dynamically deselect all filters in pivot table

[vanclute]

Hi all, haven't posted in here for a while but I have a seemingly simple issue that is driving me batty.

I have a pivot table, and I'd like to be able to "save" different views of my data. From what I gather, this isn't possible... so I figured I'd just create a few macros that select/de-select my chosen filters as desired. Simple fix right?!

Well, wheat I have found is that if you record a macro of yourself DE-SELECTING all options (by un-checking the "Select All" box) and then SELECTING the few that you want, what you actually wind up with is a macro of you selecting the items you want and one-by-one unchecking the items you don't want. It will NOT record you un-checking the "Select All" box.

What this means is that as I add more data to the pivot table, the macro will fail in the future as it will fail to un-check items that were not un-checked at the time of recording the macro.

This seems so basic, I don't want to believe that MSFT actually failed to include the ability to dynamically de-select or select all filters in a pivot table, while recording a macro.

Anyone have any input on this, or know another way to achieve the end result of having saved layouts of your pivot table data?

Thanks everyone!

Jonathan

 

[vanclute]

Oh I forgot to mention this is with Excel 2007 under Vista.

Jonathan

 

[Des1gn1ng0ne]

The all or none check box is not an actual pivot item, so I would tend to think it would be hard to reference in VB. I don't know much, but read through this. It worked for my situation...
http://www.mrexcel.com/forum/showthread.php?t=340045

Good Luck!
Brian S (Excel 2003 SP3)

 

[Des1gn1ng0ne]

You could take the code right after "on error..."

If range("A1").value like (*yourword*) then
  For Each pi In pf.PivotItems
    Select Case pi.Name
        Case "thing1","thing2","thing3"
              pi.Visible = True
    Case Else
              pi.Visible = False
    End Select
  Next pi
End if

Make as many "If Range..." groups as you need for views. You could control those from a validation list and tie the code to a worksheet_change event. Just further ideas...

 

[vanclute]

Yikes... thanks so much for taking a stab at my problem! Unfortunately your code and further explanation are a little bit beyond my current Excel/Macro know-how. Could you give a little more clarification of how exactly I would need to implement this?

I get the general idea - loop through all items currently in the filter list and uncheck them one by one. This is precisely what I would do in PHP, but that's the only language I know how to code in at all. I don't know how to get the list and act on it in VB or whatever language the macro engine uses.

Thanks!!

Jonathan

 

[Des1gn1ng0ne]

First create a named group for all of the different "saved views" (Insert/name) Then go to the tab that has your pivot table and in one of the unused cells use (Data/validation), select list and type "=name_of_saved_views_list". This gives you a way to select each view.

Right click on the sheet tab that has the pivot table and view code.

'Private Sub Worksheet_Change(ByVal Target As Range)
Sub Practice ()
 
'Use the cell that has your view list instead of B1
If Intersect(Target, Range("B1")) Is Nothing Then GoTo Last
 
Dim pt As PivotTable, pf As PivotField, pi As PivotItem
Dim B As String
 
'Use this if protection is involved in any way
If Sheets("Your Sheet").ProtectContents = True Then
   Sheets("Your Sheet").Unprotect Password:="aaa"
End If
 
'This lets the code work without updating the screen display needlessly
Application.ScreenUpdating = False
 
'You can fill this in with the info you get while recording you macro earlier.
Set pt = Sheets("Location Total").PivotTables("Contact_Total")
Set pf = pt.PivotFields("BMD")
pt.PivotCache.Refresh
 
'At no time can a pivot table have every item unchecked.
For Each pi In pf.PivotItems
        pi.Visible = True
Next pi

On Error Resume Next 'Always try to handle errors.
 
'Make sure you type in an exact match to the list.
If range("B1").value like (*yourword1*) then
  For Each pi In pf.PivotItems
    Select Case pi.Name
        'List the items you want to display in the table.  
        'Watch CaPs and spelling!
        Case "thing1","thing2","thing3"
              pi.Visible = True
    Case Else
              pi.Visible = False
    End Select
  Next pi
End if
'This is the second group of items... Same format as above, only different.
If range("A1").value like (*yourword2*) then
For Each pi In pf.PivotItems
    Select Case pi.Name
        Case B
              pi.Visible = True
    Case Else
              pi.Visible = False
    End Select
Next pi
End if
'You can copy and paste as many If...End If groups as you need
 
Set pf = Nothing
Set pt = Nothing
 
'Change the cell to match the heading cell for the info that changes.
Range("A5").Columns.AutoFit
 
'Now that the work is done, let the screen update...
Application.ScreenUpdating = True
 
'This allows you to reset the protection if used...  
Place a ' on each line if not used.  You never know when you might use it!
Sheets("Your Sheet").Protect Password:="aaa", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Your Sheet").EnableSelection = xlNoRestrictions
Last:
End Sub

Copy this into the VB Editor to read easier.
Actually, use a standard module instead of the page module to practice until it works. Use the practice title line to practice... Then whey you copy and paste the end result into the sheet code, change out the comment mark. This along with all the other examples of code should get you a bit closer. Never delete anything, just make it a comment ( add a ' to make any line a comment) until you have a working solution.

Let me know how it works out...

 

[vanclute]

Thanks so much! I wasn't entirely sure I understood, but I'm a good code hacker so I just started slicing and dicing... and came up with something that seems to work great!

My basic logic was, first reset all filters so that everything is enabled, then programmatically disable all of them except for "(blank)" (because Excel won't allow all items to be unchecked). Then go back and selectively re-enable just the items I want for the current view, and lastly turn blank back off. It works a treat! Here's the final code in case anyone else is ever trying to do this same thing...

Sub CustomView()

Dim pt As PivotTable, pf As PivotField, pi As PivotItem

Set pt = Sheets("your-sheet-name").PivotTables("your-pivot-table-name")
Set pf = pt.PivotFields("your-field-name")


Application.ScreenUpdating = False ' so the screen doesn't refresh like mad while executing
ActiveSheet.PivotTables("your-pivot-table-name").PivotFields("your-field-name").ClearAllFilters ' first set the filter state to the default of all enabled

'loop through all items in the filter list and uncheck everything but "blank"
For Each pi In pf.PivotItems
    If pi = "(blank)" Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

' re-enable just the items I want, and disable "blank"
With ActiveSheet.PivotTables("your-pivot-table-name").PivotFields("your-field-name")
    .PivotItems("AO-110219").Visible = True
    .PivotItems("AO-110471").Visible = True
    .PivotItems("AO-111211").Visible = True
    .PivotItems("AO-112221").Visible = True
    .PivotItems("AO-contextual").Visible = True
    .PivotItems("AO-KD").Visible = True
    .PivotItems("AO-cc").Visible = True
    .PivotItems("AO-Laser").Visible = True
    .PivotItems("AO-kws").Visible = True
    .PivotItems("(blank)").Visible = False
End With


On Error Resume Next 'Always try to handle errors.

'ActiveSheet.PivotTables("your-pivot-table-name").PivotFields ("your-field-name")
'.PivotItems("(blank)").Visible = True

Application.ScreenUpdating = True ' re-enable screen updates

End Sub

I'm quite sure it could be streamlined a bit more as I reference the same items (field names, table names, etc.) multiple times and they could probably be referenced by a variable instead. But since I'm not sure how to do that, this works great for now.

Thanks again for the help!

Jonathan

 

[vanclute]

Whoops, there's a couple of junk lines towards the bottom that are commented out... they can be removed. The system won't let me edit the post any longer.

Jonathan