Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to link Pivot page fields to DV Lists
I know this is pretty easy, but i'm kind of new to VBA I have found this code in Debra Dalgleish Site. It's about linking Pivot table page field to a cell dropdown list. I need it modified to do multiple page fields... Codes between $$ $$ are my attempt. 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 = "Country" $$ strField = "Location" $$ $$ strField = "Product" $$ or $$ strField = ("Country","Location","Product") $$ or $$ strField = MyRange (I have created a name range for B2:B4) $$ On Error Resume Next Application.EnableEvents = False Application.ScreenUpdating = False If Target.Address = Range("B2").Address Then $$ If Target.Address = Range("B2:B4").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 ******************* I also tried to use the same code 3 times (changing the variables to something like ws2, ws3...) but didn't work either... not sure if it's because the worksheet change event is private or... Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot page fields! | Excel Programming | |||
Conditional Linking and Formatting across worksheets to form Summary Page - Only pull/link non-null fields | Excel Worksheet Functions | |||
Pivot Table Page fields | Excel Discussion (Misc queries) | |||
Pivot Table page fields | Excel Discussion (Misc queries) | |||
Pivot Table Page Fields | Excel Discussion (Misc queries) |