Carpenter447
New Member
- Joined
- Mar 5, 2012
- Messages
- 10
Hello I have an issue that i have been playing around with my self for quite sometime now. I found a code that should work but for some reason it is not working and I haven't a clue why. Any help would be appreciated.
Goal : Code that will take a drop list and modify the pivot filters on multiple pivot tables on multiple sheets simultaneously. I.e. Drop list selection is Brown; Brown is part of the Filter Colors; All subsequent pivot tables with the same Filter for colors would then switch to brown and update the data within it.
Issue : The coding works on a sample set that I ripped from contexures.com but not for my data as it stands now.
Additional Info : I am running Excel 2007, I import that data within my pivots from an OLAP cube, & even though I work with macros quite often my actual verbatim writing skills are limited.
So the code below is an adapted version of what you can find on contextures website. I want to be able change three unique Pivot Fields simultaneously. The code string is simply triplicated below with the different fields inputted. When I use the initial code from contexture.com it works but then when i change the Pivotfield nothing happens.
Goal : Code that will take a drop list and modify the pivot filters on multiple pivot tables on multiple sheets simultaneously. I.e. Drop list selection is Brown; Brown is part of the Filter Colors; All subsequent pivot tables with the same Filter for colors would then switch to brown and update the data within it.
Issue : The coding works on a sample set that I ripped from contexures.com but not for my data as it stands now.
Additional Info : I am running Excel 2007, I import that data within my pivots from an OLAP cube, & even though I work with macros quite often my actual verbatim writing skills are limited.
So the code below is an adapted version of what you can find on contextures website. I want to be able change three unique Pivot Fields simultaneously. The code string is simply triplicated below with the different fields inputted. When I use the initial code from contexture.com it works but then when i change the Pivotfield nothing happens.
Code:
Sub UpdatePivotFields()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
'This sector changes strField1 or "Top Customer Name US"
strField1 = "Top Customer Name US"
If Target.Address = Range("D2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
'This sector changes strField2 or "_Plant"
strField2 = "_Plant"
If Target.Address = Range("M2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
'This sector changes strField3 or "Month"
strField3 = "Month"
If Target.Address = Range("U2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
End Sub
