Validation fields linked to pivot tables on separate worksheets

[daynereed]

Hi all,

Can anyone please help. I have a very basic knowledge of VB in Excel
and have found a piece of code to change any pivot table in my
workbook to match my validation drop down box. The problem is that I
wish to expand this to use a further 3 validation (drop down boxes).
Can anyone show me the extra VB code I would need?

The current working validation box is located in "Sales zone" in cell
C6

The additional fields I want to use are located in cells:

"Market Area" in cell C7
"Centre name" in cell C8
"Centre No" in cell C9


The Current VB code for working one drop down validation box is:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Sales zone"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("C6").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
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

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Debra Dalgleish]

Something like this:

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
On Error Resume Next

Select Case Target.Address
Case Range("C6").Address
strField = "Sales Zone"
Case Range("C7").Address
strField = "Market Area"
Case Range("C8").Address
strField = "Centre name"
Case Range("C9").Address
strField = "Centre No"
Case Else
GoTo exitHandler
End Select

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
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

exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

End Sub



'==============================

 

[daynereed]

Something like this:

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
On Error Resume Next

Select Case Target.Address
Case Range("C6").Address
strField = "Sales Zone"
Case Range("C7").Address
strField = "Market Area"
Case Range("C8").Address
strField = "Centre name"
Case Range("C9").Address
strField = "Centre No"
Case Else
GoTo exitHandler
End Select

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
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

exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

End Sub

'==============================

Thanks Debra!!!!