Originally Posted by
tray262
I ran into is that my pull down box will be different sheet than my pivottable.
Am I close?
Yes, you are. The implementation remains same only need to provide valid references. So if I say, let "FilterSheet" be the sheet where you have the filter and "PivotData" is the sheet where we have the PivotTable, then you'll have to first track where you'll be making the change..?? That's "Filtersheet" sheet's Range("C1") of course.. yes, so will have to write the code in this sheet's Change event. I believe that's what you've done. So, +1 point buddy!!
Now, to adapt the changes, you'll have to keep track of two things - mainly Worksheet name and Range that we'll be providing. Before that, a brief introduction to the generic code's parameters:
1. sht As Worksheet - This is the sheet where the Pivot table resides. So in this case, its "PivotData"
2. PvtTABName as String - Is the name of the PivotTable. To get the name, select the Pivot table. You'll see two new tabs "Options" and "Design" has appeared at the end of the ribbon with a heading "PivotTable Tools", just click "Options". You'll find the pivot table name on the first left field.
3. FieldName as String - Is the name of the field (basically where you wish to perform the filter). In this case is "Assessments" column. Btw, Fields are columns and rows are records.. just FYI.
4. Finally FilterString As String - is the string based on which you want to perform the filtering. In my attached example, these were the values of "Assessments" column/field.
Now, let see how the macro is gonna work for you. Firstly, as you'll be making changes to the sheet which is not containing the PivotTable, we'll first have to get to the sheet where we have the Pivot Table. So for that, will do..
Well, it seems I and the computer is now on the same sheet. So, now I need to direct the computer to the column on which I need to perform the operation. I know where it is, as I have its address.. so I'll create a PivotField variable to hold the location by using the following statement..
Now even computer is holding the same address, so what are we waiting for..? Lets do the operations!
1. Orientation basically defines location of the field/column. You might have seen Four sections on the Field List pane. Report filter is what we use for filtering purposes - which is what we have used in the example too. This property returns or sets the xlPivotFieldOrientation Enumeration which are xlPageField also 3 (Filter), xlRowField also 1 (Row Labels), xlDataField also 4 (Values), xlColumnField also 2 (Column Labels), and xlHidden also 0 (Not visible or checked).
2. Alright, now let's walkthrough .Position = 1. It simply tells the computer to perform the defined check before any other. You might seen in the pivot rows section that the first item in the row gets first highlighted and becomes the root of any other nodes that comes after it.
3. ClearAllFilters is a piece of code to make sure no filters are applied before I put any filter on the pivot fields.
4. And now, the finale.. basically, just .CurrentPage = FilterString would do, but am making sure if no filter is provided then pivot needs to come back to normal. In other words, apply filter only if there is any condition applied, if not, make it normal.
Hope that clears all working of my codes..
Here's the updated code for the FilterFields
and for FilterSheet's worksheet change event..
Please note, am using Sheet3 or can use Sheets("PivotData") to run the macro. Earlier you were getting error coz it was ActiveSheet which means "FilterSheet" and there we had no Pivot table so you were getting the error. Attaching the reworked sample file for your references.
P.S. you can use Application.ScreenUpdating = False to avoid flickering and set it back to True when done running macro. Google about it you will find it handy in use.
Bookmarks