controlling which column is shown on multiple pivot tables

[Dan100]

I have a spreadsheet with multiple pivot tables on each worksheet. Each of the pivots has all my patients in the column area of the pivot.

However I only show one column, or one patient, at a time on each pivot. If I want to change the patient I'm viewing stats for I have to go through all my pivots and manually change the columns 'show' drop down menu.

Is there a way I can change which column is shown on all the pivot tables in one command (or at least a quicker way than changing all the pivots manually).

Any help would be much appreciated.

Thanks

 

[SydneyGeek]

Hi Dan100, welcome to the Board!

You may find that it's easier to operate if your patients are set up as Page fields rather than Column fields, if you only want to see one patient at a time. The reason I say that, is that in the code for Column or Row fields, you need to first display all items, then successively turn them off to display only one. With Page fields, all you need to do is refresh the cache.

For the purposes of this example, I have set up 3 pivot tables with their page fields in B3, F3 and J3. To synchronise them, this code needs to go in the worksheet's code module (right-click the sheet tab, View Code, and paste this VBA code):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PT As PivotTable
    'ensure that B3 is the only trigger cell
    If Intersect(Target, Range("B3")) Is Nothing Then
        Exit Sub
    Else
        'turn off events
        Application.EnableEvents = False
        
        'update the page fields in the other PTs
        Range("F3").Value = Range("B3").Value
        Range("J3").Value = Range("B3").Value
        
        'refresh the PTs
        For Each PT In ActiveSheet.PivotTables
            PT.PivotCache.Refresh
        Next PT
        
        'turn events back on
        Application.EnableEvents = True
    End If
End Sub

You can adjust cell references to suit.

Denis

 

[Dan100]

Thanks!

Thanks very much for taking the time out to help me on this one. I'm a bit new to pivot tables and I've never gone near Visual Basic!

I'll give it a go once I've put the patients into the page field (I have about 46 pivots on each sheet, looking at various aspects of patient information,so it may take some time !)

 

[sehested]

I have the same problem as Dan100 but i need to have more than one column in each pivot table so i cant use options with pagefields. I need to show years in the columns so i can compare a range of years. I have tried to make the code below but it doesnt work as it should

'Here i have tried to add the columns from my master pivot to one of the other pivot tables in my workssheet
    'The problem is that is just add them and dont delete the ones thats already there so the list get infinity
    For i = 1 To Sheets("Pivot").PivotTables("PivotTable1").DataFields.Count
        Sheets("Energy pivot").PivotTables("PivotTable3").AddDataField Sheets("Energy pivot").PivotTables("PivotTable3").PivotFields(Mid(Sheets("Pivot").PivotTables("PivotTable1").DataFields(i).Name, 8, 6))
        'Several other pivot tables are changed in the same way
    Next

The problem with this code is that it just adds the columns to the end and the pivot table will go to infinity at the end. I have tried to use something with .Orientation = xlHidden but i had no luck with it Im using Excel 2003.
Can anyone make a code to ilustrate what i can do to solve the problem?

 

[Herbert Seidenberg]

Excel 2010 PivotTable
Filter PageFields, ColumnFields or RowFields
of multiple PivotTables on multiple tabs
with one or more PivotItem(s) selected from a Table.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_22_10.xlsm
Pdf preview:
http://www.mediafire.com/file/kn51u0iznmy/04_22_10.pdf

 

[sehested]

Excel 2010 PivotTable
Filter PageFields, ColumnFields or RowFields
of multiple PivotTables on multiple tabs
with one or more PivotItem(s) selected from a Table.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_22_10.xlsm
Pdf preview:
http://www.mediafire.com/file/kn51u0iznmy/04_22_10.pdf

Hi Herbert
I really cant get this to work because i use excel 2003 as i wrote in my post And from what i can read from the code this takes something with the page fields which i dont think i need I only need to have all pivot tables in the workbook to reflect what columns that are showed in the master pivot
I have tried to show what i mean in this picture.
http://www.mediafire.com/file/qngzlntrzzz/pivottable.jpg

 

[Herbert Seidenberg]

Excel 2010 PivotTable
Use PivotTable Type 2,
then the columns become ColumnFields
instead of DataFields and you can use my macro.
PT Type comparison:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_04_10.xlsx
As for Excel 2003, notice today's date: 07/11/2010 AD