Choosing a different filter option for a pivot table outside of the pivot table

[Surreybloke]

Hi,

I was wondering if it is possible to be able to change the option within a filter for a pivot table without having to click on the filter option itself. What I would like to be able to do if it is possible, is to select an employee name or ID via cells that are not part of the pivot, and that once done, it will automatically change the data in the pivot based on the change of name/ID. I would potentially like this to happen for two pivot's at the same time, in the same spreadsheet.

Many thanks in advance for any help.

 

[Surreybloke]

I should add that the reason for this request is that I want to make it as simple as possible for sales people to use, rather than getting them to manually update the pivot themselves.

 

[RichardMGreen]

I had a similar question a few weeks ago and came up with this:-

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changer As String, pivots As PivotItem
    If Target.Address = "$B$2" Then
        changer = Sheets("FRONT END").Range("B2")
        Sheets("PIVOT").PivotTables("PivotTable4").PivotFields("pkLocation").PivotItems(changer).Visible = True
       
        For Each pivots In Sheets("PIVOT").PivotTables("PivotTable4").PivotFields("pkLocation").PivotItems
            If pivots = changer Then
                Sheets("PIVOT").PivotTables("PivotTable4").PivotFields("pkLocation").PivotItems(changer).Visible = True
            Else
                Sheets("PIVOT").PivotTables("PivotTable4").PivotFields("pkLocation").PivotItems(pivots.Name).Visible = False
            End If
        Next
        Sheets("PIVOT").PivotTables("PivotTable4").PivotCache.Refresh
    End If
End Sub

In this case, B2 was a data validation cell (i.e. a drop-down menu) and, when that was changed, altered the filter on the pivot table.

You;'d have to alter the code above to suit your exact requirements.

 

[Surreybloke]

Hi,

Thanks for forwarding that on.

I've amended the report I'm creating to accommodate having the data validation cell on a separate sheet as my original preference was to have it at the top of the sheet with the two pivots on there. I have also amended the B2 to another cell based on what I have created. Unfortunately, despite making the adjustments to your code based on my setup, nothing is happening when I change the employee name on the data validation cell.

I have saved the file as a binary file which I know usually allows macros to work. It doesn't display the macro/code when you go to view macros within Excel but I'm assuming this is more like a persistent macro that should automatically work each time you select a new name.

I was wondering if you have any further thoughts on what I might be missing?

 

[RichardMGreen]

You'll probably have to change this line:-

Sheets("PIVOT").PivotTables("PivotTable4").PivotFields("pkLocation").PivotItems(changer).Visible = True

to match the name of your pivot table and change the name of the pivot fields to match your pivot table filter as well.
The easiest way I've found to find out what they are is to record a macro doing the change and then check the code for the relevant bits.

You'll also have to change each of the pivot table names and pivot fields in the code to match yours.

Also, macros are disabled by default. You'll need to check the options and make sure that you either enable them by default (not recommended) or to give you a warning with the option to enable them.

 

[Surreybloke]

This is the re-adjusted code for what I put down earlier. I've highlighted those sheet names, etc which I've amended. I retained the "FRONT END" name for now.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changer As String, pivots As PivotItem
If Target.Address = "$J$14" Then
changer = Sheets("FRONT END").Range("J14")
Sheets("PIVOTS").PivotTables("PivotTable1").PivotFields("pkLocation").PivotItems(changer).Visible = True

For Each pivots In Sheets("PIVOTS").PivotTables("PivotTable1").PivotFields("pkLocation").PivotItems
If pivots = changer Then
Sheets("PIVOTS").PivotTables("PivotTable1").PivotFields("pkLocation").PivotItems(changer).Visible = True
Else
Sheets("PIVOTS").PivotTables("PivotTable1").PivotFields("pkLocation").PivotItems(pivots.Name).Visible = False
End If
Next
Sheets("PIVOTS").PivotTables("PivotTable1").PivotCache.Refresh
End If
End Sub

I've recorded a quick macro to ensure that it was definitely PivotTable1, which it is.

Sub Test()
'
' Test Macro
'


'
ActiveSheet.PivotTables("PivotTable1").PivotFields("Person Name2").CurrentPage _
= "(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Person Name2")
.PivotItems("Person A ").Visible = False
.PivotItems("Person B ").Visible = True
End With
End Sub

With this macro, all I did was record myself changing the drop down filter in the pivot table for the person name (header in the data of Person Name2).

 

[RichardMGreen]

No problem.

You'll now need to change all instance of "pkLocation" in my original code to "Person Name2" in your code.
That should then be you sorted.

The Front End sheet will need to be created (unless you've done it already) and that will hold the drop-down list of the options you want to have.

 

[Surreybloke]

I've made that amendment but it's still not changing the filter selection for the pivot table for some reason.

I've been into options, privacy settings and Macro settings, and tried selecting Enable all macros, but that hasn't made it work either so I'm confused now about what is still leading it to not work when everything seems correct.

 

[RichardMGreen]

Not off the top of my head unfortunately.

Can you post your current code?
Also, where did you create the code? Was it in the sheet VBA or in a module?

 

[Surreybloke]

I added it in via ALT + F11. I just realised that by adding the code to the specific sheet rather than just "ThisWorkbook" and testing it that it has enabled it to work.

I want it to do the same thing to the second pivot, so is it easier to paste the same code below this, and change the PivotTable number from 1 to 2 in order to make it change that as well for me?

This is the current code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changer As String, pivots As PivotItem
If Target.Address = "$J$14" Then
changer = Sheets("Front End").Range("J14")
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(changer).Visible = True

For Each pivots In Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems
If pivots = changer Then
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(changer).Visible = True
Else
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(pivots.Name).Visible = False
End If
Next
Sheets("Pivots").PivotTables("PivotTable1").PivotCache.Refresh
End If
End Sub