Pivot tables: auto-update 1 when other is changed

  • Hi,
    I have 3 pivot tables on the same page - they all have the same PAGE and ROW fields, just different column and data fields. (may seem weird, but I'm producing reports for non-Excel people and this seems the only way to display info in user-friendly way).

    Problem: If a user changes the selection in a Page field, I need the other 2 pivot tables to automatically select the same Page field, so the pivot tables are still all like-for-like.

    I've started writing a macro which I can run to do this - below - but
    1) I need it to run automatically when any of the page or row fields are updated
    2) my macro is probably far too basic for my needs - one of the page fields has 245 values, so the below isn't really going to cut it!! ;) ... any suggestions to improve this would be greatly appreciated.

    I've done the below in the Sheet code to try and auto-run it, but it doesn't work.

    Private Sub Auto()
    Call PivotMatch1 
    End Sub

    Thanks for any help. :)
    btw - can anyone recommend a good book so I can develop my macro skills?

  • Re: Pivot tables: auto-update 1 when other is changed

    Hi Dave, I would put this code in the worksheet were is located the pivot tables

  • Re: Pivot tables: auto-update 1 when other is changed

    Hi. I believe what you want is to syncronise the pages of a series of pivot tables. I had a go and came up with this in the sheet module :-

    It is not perfect because when you choose "all" followed by another selection it jumps out but maybe that is because I screwed up the pivot cache playing around ?

    I set it to react to a change in the first PT. I guess you could use the target name to find which PT has changed then changed the other two. Anyway hope it gets you started and if you get stuck post back.

    You also need to think about how to deal with multi selections from one field.


  • Re: Pivot tables: auto-update 1 when other is changed


    jetted - thanks for your post, but I think you may have misunderstood ... the base data for the pivot tables is not changing - it's just the viewing options for the pivot tables.

    Carlmack - thanks for the code - I tried it but strangely it seemed to work once - the first time - and then wouldn't work again after that.

    Unfortunately I think I'm a bit out of my depth here, but I wrote a better normal macro to match the pivot tables, so I'll just place a button next to Pivot Table 1 and users can activate it by that.

    Many thanks for your efforts.
    Best regards,

  • Re: Pivot tables: auto-update 1 when other is changed


    For reference.

    If you stopped the macro in the middle you could have disabled events in which case it would not run again (in fact no events will run). You can switch them on again with :

    Sub EnableEvents()
    Application.EnableEvents = True 
    End Sub

    Anyway you seem to have a solution that works ...


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!