controlling which column is shown on multiple pivot tables

Dan100

New Member
Joined
Oct 5, 2006
Messages
2
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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):

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

New Member
Joined
Oct 5, 2006
Messages
2
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

New Member
Joined
Jul 1, 2010
Messages
42
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
Code:
'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? :)
 
Last edited:

sehested

New Member
Joined
Jul 1, 2010
Messages
42
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 :confused: 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
 

Forum statistics

Threads
1,136,649
Messages
5,676,989
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top