Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated .PivotItems boolean value
Hi, I got 3 sheets (data, sheetA and sheetB) int he same worksheet.
Both got a pivottable (pivotA and pivotB). The data form PivotB depends on PivotA (which repends on the data from data sheet) I got on both pivot a Row Field called dates. I would like to, when a user check mark ie: 19-Oct and 18-Oct on pivotA, pivotB's Row Field Items get checked automaticaly. I've done it for the Pivot Page fields but not the Row Field. I've recorded the action of changing an item in row fields and I got ..PivotItems("19-Oct").Visible = True ..PivotItems("18-Sep").Visible = True So what I'm trying to do is gather all item selected in .PivotItems from pivotA and replicate them on pivotB thank you a bunch! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated .PivotItems boolean value
On Nov 5, 3:44 pm, magickarle wrote:
Hi, I got 3 sheets (data, sheetA and sheetB) int he same worksheet. Both got a pivottable (pivotA and pivotB). The data form PivotB depends on PivotA (which repends on the data from data sheet) I got on both pivot a Row Field called dates. I would like to, when a user check mark ie: 19-Oct and 18-Oct on pivotA, pivotB's Row Field Items get checked automaticaly. I've done it for the Pivot Page fields but not the Row Field. I've recorded the action of changing an item in row fields and I got .PivotItems("19-Oct").Visible = True .PivotItems("18-Sep").Visible = True So what I'm trying to do is gather all item selected in .PivotItems from pivotA and replicate them on pivotB thank you a bunch! Hi, I've created a macro which does it: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Application.EnableEvents = False pt = ActiveWorkbook.Worksheets(2).PivotTables(1) Dim PTFld As PivotField Dim PTItemStat As Boolean Set pt = Target Set pvtTable = Worksheets("Sheet2").Range("A4").PivotTable For Each pvtfileds In pvtTable.PivotFields If pvtTable.PivotFields(pvtfileds.Name).Orientation < xlHidden Then For Each pvtitem In pvtTable.PivotFields(pvtfileds.Name).PivotItems PivotTables("Pivottable2").PivotFields(pvtfileds.N ame).PivotItems(pvtitem.Name).Visible = pvtTable.PivotFields(pvtfileds.Name).PivotItems(pv titem.Name).Visible PivotTables("Pivottable3").PivotFields(pvtfileds.N ame).PivotItems(pvtitem.Name).Visible = pvtTable.PivotFields(pvtfileds.Name).PivotItems(pv titem.Name).Visible Next pvtitem End If Next pvtfileds Application.EnableEvents = True Exit Sub End Sub So what it does, for each fields, it will match their PivotItems.visible status to the pivottable2 and 3. Now, I'm getting a slow running macro that is not optimized based on which field was changed (which I would like to do) So in other word: I would like to know which filed the user changed so I can optimize the macro. IE: User changes Month filed from "All" to "November". The Macro that I got right now will run on all Fields even if the user only changed the Month one (and that is a problem because in the "Day" field, I got over 400 items) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PIvotitems visible | Excel Programming | |||
Help setting PivotItems... | Excel Programming | |||
PivotItems Bug?! | Excel Programming | |||
Adding PivotItems | Excel Programming | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |