Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
Hello,
I have two pivot table on a same sheet with the same drop down list on the top selection (not row or column). Is it possible to select from a selection list from one pivot and automatically select the same selection list from the second pivot table? If it is how to achieve it. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
the code belongs to the sheet's code page.
"Cam" wrote in message ... Thanks for your help, I got it. "Cam" wrote: Hi Patrick, Thank for the sugguestion. I tried your code and it is not updating the other pivot table 2 when the pivot table 1 field is selected. Do I put the code in a module or where? Any way, this is the code I put in a module. Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check which table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("WC").CurrentPage source.PivotFields("WC").CurrentPage = text Application.EnableEvents = True End Sub "Patrick Molloy" wrote: this can be done. My example isn't 'pretty' but will give the general idea in the sheet events, you can trap the Pivot Table Update Event so i have two tables where the page field that I'm changing is in the pivot field called 'Set' in both pivots....the fields can differ, so long as the data is the same The event passes the changed pivot through to the handler in the variable named 'Target' I check which table changes, and set a pivot table object, Source, to the other pivot table Now, when I select a value from the page drop down on either pivot, when that pivot refreshes, the handler maked the same change to the other pivot. Note that this 2nd change would also fire the event handler, so we'd go into an infinite loop -- so i switch event handling off for this change, then switch it on again afterwatds Here's my code: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check whic table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("SET").CurrentPage source.PivotFields("SET").CurrentPage = text Application.EnableEvents = True End Sub "Cam" wrote in message ... Hello, I have two pivot table on a same sheet with the same drop down list on the top selection (not row or column). Is it possible to select from a selection list from one pivot and automatically select the same selection list from the second pivot table? If it is how to achieve it. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel
workbook remove the NOSPAM bit ;) "Cam" wrote in message ... Thanks for your help, I got it. "Cam" wrote: Hi Patrick, Thank for the sugguestion. I tried your code and it is not updating the other pivot table 2 when the pivot table 1 field is selected. Do I put the code in a module or where? Any way, this is the code I put in a module. Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check which table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("WC").CurrentPage source.PivotFields("WC").CurrentPage = text Application.EnableEvents = True End Sub "Patrick Molloy" wrote: this can be done. My example isn't 'pretty' but will give the general idea in the sheet events, you can trap the Pivot Table Update Event so i have two tables where the page field that I'm changing is in the pivot field called 'Set' in both pivots....the fields can differ, so long as the data is the same The event passes the changed pivot through to the handler in the variable named 'Target' I check which table changes, and set a pivot table object, Source, to the other pivot table Now, when I select a value from the page drop down on either pivot, when that pivot refreshes, the handler maked the same change to the other pivot. Note that this 2nd change would also fire the event handler, so we'd go into an infinite loop -- so i switch event handling off for this change, then switch it on again afterwatds Here's my code: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check whic table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("SET").CurrentPage source.PivotFields("SET").CurrentPage = text Application.EnableEvents = True End Sub "Cam" wrote in message ... Hello, I have two pivot table on a same sheet with the same drop down list on the top selection (not row or column). Is it possible to select from a selection list from one pivot and automatically select the same selection list from the second pivot table? If it is how to achieve it. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
Patrick,
Thanks for your help. I do have another question. What do I need to change in the code if pivottable1 and pivottable2 are in a different worksheet? "Patrick Molloy" wrote: write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel workbook remove the NOSPAM bit ;) "Cam" wrote in message ... Thanks for your help, I got it. "Cam" wrote: Hi Patrick, Thank for the sugguestion. I tried your code and it is not updating the other pivot table 2 when the pivot table 1 field is selected. Do I put the code in a module or where? Any way, this is the code I put in a module. Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check which table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("WC").CurrentPage source.PivotFields("WC").CurrentPage = text Application.EnableEvents = True End Sub "Patrick Molloy" wrote: this can be done. My example isn't 'pretty' but will give the general idea in the sheet events, you can trap the Pivot Table Update Event so i have two tables where the page field that I'm changing is in the pivot field called 'Set' in both pivots....the fields can differ, so long as the data is the same The event passes the changed pivot through to the handler in the variable named 'Target' I check which table changes, and set a pivot table object, Source, to the other pivot table Now, when I select a value from the page drop down on either pivot, when that pivot refreshes, the handler maked the same change to the other pivot. Note that this 2nd change would also fire the event handler, so we'd go into an infinite loop -- so i switch event handling off for this change, then switch it on again afterwatds Here's my code: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check whic table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("SET").CurrentPage source.PivotFields("SET").CurrentPage = text Application.EnableEvents = True End Sub "Cam" wrote in message ... Hello, I have two pivot table on a same sheet with the same drop down list on the top selection (not row or column). Is it possible to select from a selection list from one pivot and automatically select the same selection list from the second pivot table? If it is how to achieve it. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table help
its easier since you don't need to use the IF to determine which pivot table
changed Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String Set source = PivotTables("PivotTable2") Application.EnableEvents = False text = Target.PivotFields("WC").CurrentPage source.PivotFields("WC").CurrentPage = text Application.EnableEvents = True End Sub for the sheet containing pivottable2 change Set source = PivotTables("PivotTable2") to Set source = PivotTables("PivotTable1") "Cam" wrote in message ... Patrick, Thanks for your help. I do have another question. What do I need to change in the code if pivottable1 and pivottable2 are in a different worksheet? "Patrick Molloy" wrote: write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel workbook remove the NOSPAM bit ;) "Cam" wrote in message ... Thanks for your help, I got it. "Cam" wrote: Hi Patrick, Thank for the sugguestion. I tried your code and it is not updating the other pivot table 2 when the pivot table 1 field is selected. Do I put the code in a module or where? Any way, this is the code I put in a module. Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check which table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("WC").CurrentPage source.PivotFields("WC").CurrentPage = text Application.EnableEvents = True End Sub "Patrick Molloy" wrote: this can be done. My example isn't 'pretty' but will give the general idea in the sheet events, you can trap the Pivot Table Update Event so i have two tables where the page field that I'm changing is in the pivot field called 'Set' in both pivots....the fields can differ, so long as the data is the same The event passes the changed pivot through to the handler in the variable named 'Target' I check which table changes, and set a pivot table object, Source, to the other pivot table Now, when I select a value from the page drop down on either pivot, when that pivot refreshes, the handler maked the same change to the other pivot. Note that this 2nd change would also fire the event handler, so we'd go into an infinite loop -- so i switch event handling off for this change, then switch it on again afterwatds Here's my code: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim source As PivotTable Dim text As String 'check whic table changed, we'll make the same change to the other If Target.Name = "PivotTable1" Then Set source = PivotTables("PivotTable2") Else Set source = PivotTables("PivotTable1") End If Application.EnableEvents = False text = Target.PivotFields("SET").CurrentPage source.PivotFields("SET").CurrentPage = text Application.EnableEvents = True End Sub "Cam" wrote in message ... Hello, I have two pivot table on a same sheet with the same drop down list on the top selection (not row or column). Is it possible to select from a selection list from one pivot and automatically select the same selection list from the second pivot table? If it is how to achieve it. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Print Pivot Table headers on pages with Pivot Table | Excel Worksheet Functions | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |