VBA To Control PivotTable Filtering

[MikeG]

I have the following code that helps a user to control a PivotTable without having to use the dropdown filter. In a cell called "Selected_Store1", the user enters a store number - e.g. 347. They then hit a button that triggers the code below.

k = Range("Selected_Store1")

With ActiveSheet.PivotTables("PivotTable12").PivotFields("Store #")
.PivotItems(k).Visible = True
End With


I have two things I would like help on.

First, how can I clear any existing stores that are checked in the drop down filter box? (If I am doing it manually, I either uncheck the store(s) in question, or check Select All, then click Select All to clear.)

Secondly, the code

.PivotItems(k).Visible = True

worked OK when I hard code in a value such as:
.PivotItems("347").Visible = True

but does not work when I use the variable k. How can I make it so that k is read properly as a string (I guess)?

Thanks,

MikeG

 

[Jerry Sullivan]

Hi MikeG,

This thread has some code that you can use and a discussion of some of the issues that need to be addressed when using VBA to set PivotTable filters.

http://www.mrexcel.com/forum/showthread.php?t=566046

Just let me know if you want any help adapting this to your application.

 

[MikeG]

Hi MikeG,

This thread has some code that you can use and a discussion of some of the issues that need to be addressed when using VBA to set PivotTable filters.

http://www.mrexcel.com/forum/showthread.php?t=566046

Just let me know if you want any help adapting this to your application.

Thanks Jerry - very useful.

Mike