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.

 

[RichardMGreen]

Glad you got the first one working.
You can. Again, you'll need to check the pivot table number and field names you want to change.
If you're using the same field on "Front End" to control both pivots then that's all that will need changing.
If not, you'll need to copy just about everything into the same event but change the cell reference to the new one.

 

[Surreybloke]

I've tried adding in the same code below the original code, adjusting the PivotTable1 to PivotTable2, but I'm getting the following error: Compile error: Ambiguous name detected: Worksheet_Change

This is the bit of code highlighted: Private Sub Worksheet_Change(ByVal Target As Range

I'm not sure what further amendment I need to make in order for it not to be treated as a duplicate of the first section of code.

 

[RichardMGreen]

Ah, no problem.

You can't have two worksheet_change events on the same sheet.
What you would need is something like this:-

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)
[/COLOR]' Pivot Table 1
[COLOR=#333333]Dim changer As String, pivots As PivotItem[/COLOR]
[COLOR=#333333]If Target.Address = "$J$14" Then[/COLOR]
[COLOR=#333333]changer = Sheets("Front End").Range("J14")[/COLOR]
[COLOR=#333333]Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(changer).Visible = True[/COLOR]

[COLOR=#333333]For Each pivots In Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems[/COLOR]
[COLOR=#333333]If pivots = changer Then[/COLOR]
[COLOR=#333333]Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(changer).Visible = True[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(pivots.Name).Visible = False[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]Sheets("Pivots").PivotTables("PivotTable1").PivotCache.Refresh[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]
[/COLOR]' Pivot Table 2
[COLOR=#333333]If Target.Address = "$J$14" Then[/COLOR]
[COLOR=#333333]changer = Sheets("Front End").Range("J14")[/COLOR]
[COLOR=#333333]Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name2").PivotItems(changer).Visible = True[/COLOR]

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

The you would just change the relevant bits in the second block for pivot table 2 to whatever you need it to be.

 

[Surreybloke]

Here is the code that I've now got in based on what you've suggested above:

I've now called "Front End", "Select employee name instead", and also "Person Name2" from the first pivot needs to be "SSO Offering Owner" for the second pivot, which I've amended accordingly. However, neither pivot is updating when I change the name on the validation list on the Select employee name sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
' Pivot Table 1
Dim changer As String, pivots As PivotItem
If Target.Address = "$J$14" Then
changer = Sheets("Select employee name").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


' Pivot Table 2
If Target.Address = "$J$14" Then
changer = Sheets("Select employee name").Range("J14")
Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(changer).Visible = True


For Each pivots In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems
If pivots = changer Then
Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(changer).Visible = True
Else
Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(pivots.Name).Visible = False
End If
Next
Sheets("Pivots").PivotTables("PivotTable2").PivotCache.Refresh
End If


End Sub

 

[RichardMGreen]

I can't see anything obviously wrong in your code, so it might be worth putting the STOP command just after the DIM.
That will allow you to step through the code using F8 and see where it goes wrong.

 

[Surreybloke]

The debug came up with this as the first problem...

Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(changer).Visible = True

It highlighted the whole row as above. When I re-ran the code, it correctly updated the employee name for the first pivot, but at the point where it tried to update the name for the second pivot, it stopped. I double checked that the name of the second pivot table is as above in the same way as I did yesterday.

 

[RichardMGreen]

That is potentially because the second pivot table, field "SSO Offering Owner" doesn't contain the value in the relevant cell.

 

[Surreybloke]

I had a thought that I reckoned might resolve it but hasn't unfortunately. I realised that for the employee names from the first set of data that they each had a space after each name which doesn't exist for the employee names in the second set of data. I thought that the code might be not recognising the names as being the same. I created a third person name column in the first set of data and added a trim formula which has removed that space. I've re-run the code but it's now failing at the first hurdle this time instead.

Private Sub Worksheet_Change(ByVal Target As Range)
' Pivot Table 1
Dim changer As String, pivots As PivotItem
If Target.Address = "$B$2" Then
changer = Sheets("Select employee name").Range("B2")
Sheets("Pivots").PivotTables("PivotTable1").PivotFields("Person Name3").PivotItems(changer).Visible = True


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


' Pivot Table 2
If Target.Address = "$B$2" Then
changer = Sheets("Select employee name").Range("B2")
Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(changer).Visible = True


For Each pivots In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems
If pivots = changer Then
Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(changer).Visible = True
Else
Sheets("Pivots").PivotTables("PivotTable2").PivotFields("SSO Offering Owner").PivotItems(pivots.Name).Visible = False
End If
Next
Sheets("Pivots").PivotTables("PivotTable2").PivotCache.Refresh
End If


End Sub

 

[RichardMGreen]

From what you've pout, the names are slightly different on each table (either with an additional space or without).
In order for the code to work, the name you select must match the pivot field value exactly (whether that includes a space or not).

If possible, I'd get the initial data and remove and additional spaces from it, then refresh the pivot tables manually (both of them) and lastly rebuild your lists on "Select employee name".