Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple pivots updated with selection in one table
Also posted in Worksheet Functions, but maybe programming is only way to go...
I have multiple pivots (all taking data link from the first pivot table) on one sheet. Each sheet will have a "Salesperson" page field. I'd like to be able to drop down the first table and select the salesperson and have all the other tables automatically do the same. Anyone? -- Boris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple pivots updated with selection in one table
If I remember correctly, Worksheet_Change event does not work on pag fields in a pivot table. The way I would approach this is to add a validation cell with drop-down box that automatically gets populated with the values fro the page field, and then use the Worksheet_Change event for that cel to change the page fields in VBA -- Kre ----------------------------------------------------------------------- Krem's Profile: http://www.excelforum.com/member.php...fo&userid=2434 View this thread: http://www.excelforum.com/showthread.php?threadid=37939 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple pivots updated with selection in one table
I am not conversant enough in VBA for that level. Have only done basic level.
If you have anything you can provide, I'd be grateful. -- Boris "Krem" wrote: If I remember correctly, Worksheet_Change event does not work on page fields in a pivot table. The way I would approach this is to add a validation cell with a drop-down box that automatically gets populated with the values from the page field, and then use the Worksheet_Change event for that cell to change the page fields in VBA. -- Krem ------------------------------------------------------------------------ Krem's Profile: http://www.excelforum.com/member.php...o&userid=24344 View this thread: http://www.excelforum.com/showthread...hreadid=379391 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple pivots updated with selection in one table
Hi, try adapting the following. You will need to change the code slightly depending on the number of pivot tables you have, the sheet name that the pivots are on, and the pivotfield that you want to make the "change field" - I think you said it was called salesperson ? Hope this helps. Simply put it in a worksheet selectionchange sub routine if you want it to update automatically. Private Sub Worksheet_SelectionChange(ByVal Target As Range) code here End Sub Sub AssimilatePivots() Dim pvt As PivotTable Dim pvt2 As PivotTable Set pvt = Me.PivotTables(1) Set pvt2 = Sheets("Your sheet name").PivotTables(2) If LCase(pvt.PivotFields("Salesperson").CurrentPage) _ < LCase(mvPivotPageValue) Then 'The Page Field was changed Application.EnableEvents = False pvt.RefreshTable mvPivotPageValue = _ pvt.PivotFields("Salesperson").CurrentPage pvt2.PageFields("Salesperson").CurrentPage _ = mvPivotPageValue Application.EnableEvents = True End If Range("a1").Select End Sub -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=379391 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple selection on pyvot table. | Excel Discussion (Misc queries) | |||
Data Validation with multiple table selection | Excel Discussion (Misc queries) | |||
multiple pivots updated with selection in one table | Excel Worksheet Functions | |||
Multiple selection in the page area of a pivot table | Excel Discussion (Misc queries) | |||
Pivots using other pivots and dynamic query problem | Excel Programming |