Hi,
I have a pivot table in "Inno" worksheet that i'd like to control from drop down box in "Summary" sheet. I created a name range for the dropdown box called "Region"I have below code however it is not correctly running nor giving me any error message. Can you please help?
I have a pivot table in "Inno" worksheet that i'd like to control from drop down box in "Summary" sheet. I created a name range for the dropdown box called "Region"I have below code however it is not correctly running nor giving me any error message. Can you please help?
Code:
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
Dim strField2 As String
strField = "Region"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = ThisWorkbook.Sheets("Summary").Range("Region").Address Then
Set ws = ThisWorkbook.Sheets("Inno")
For Each pt In ws.PivotTables("PivotTable1")
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
Last edited: