none
Change to multiple pivot tables in Excel sheet RRS feed

  • Question

  • <p>Hello everyone</p><p>I have the following code which changes around 10 pivot tables in an excel sheet when i change the date field in anyone of the pivots. How could i adapt this code so that one of the pivots (lets say PivotTable1) controlled the date for half the pivots on the sheet, and another of the pivots (PivotTable2) controlled the other half? </p><p></p><p>Option Explicit<br />Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)<br />On Error Resume Next<br />Dim wsMain As Worksheet<br />Dim ws As Worksheet<br />Dim ptMain As PivotTable<br />Dim pt As PivotTable<br />Dim pfMain As PivotField<br />Dim pf As PivotField<br />Dim pi As PivotItem<br />Dim bMI As Boolean</p><p>On Error Resume Next<br />Set wsMain = ActiveSheet<br />Set ptMain = Target</p><p>Application.EnableEvents = False<br />Application.ScreenUpdating = False</p><p>Set pfMain = ptMain.PivotFields("Date")<br />bMI = pfMain.EnableMultiplePageItems<br />For Each pt In wsMain.PivotTables<br />&nbsp;&nbsp;&nbsp; If pt &lt;&gt; ptMain Then<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pt.ManualUpdate = True<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set pf = pt.PivotFields("Date")<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bMI = pfMain.EnableMultiplePageItems<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; With pf<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .ClearAllFilters<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Select Case bMI<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case False<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .CurrentPage = pfMain.CurrentPage.Value<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case True<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .CurrentPage = "(All)"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For Each pi In pfMain.PivotItems<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .PivotItems(pi.Name).Visible = pi.Visible<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next pi<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .EnableMultiplePageItems = bMI<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End Select<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End With<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bMI = False<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set pf = Nothing<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pt.ManualUpdate = False<br />&nbsp;&nbsp;&nbsp; End If<br />Next pt<br />&nbsp;&nbsp;&nbsp; <br />Application.EnableEvents = True<br />Application.ScreenUpdating = True</p><p>End Sub</p>
    Friday, August 10, 2012 9:55 AM

All replies

  • @Moderators

    Pls see above.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 10, 2012 11:04 AM
    Answerer
  • Hi

    Thanks for the quick response but I can't read the actual code you sent.

    BO 

    Friday, August 10, 2012 1:23 PM
  • Your Post seems to be like binary memory dump in my browser.Can you repost ?


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 10, 2012 3:07 PM
    Answerer
  • Option Explicit

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    On Error Resume Next

    Dim wsMain As Worksheet

    Dim ws As Worksheet

    Dim ptMain As PivotTable

    Dim pt As PivotTable

    Dim pfMain As PivotField

    Dim pf As PivotField

    Dim pi As PivotItem

    Dim bMI As Boolean

    On Error Resume Next

    Set wsMain = ActiveSheet

    Set ptMain = Target

    Application.EnableEvents = False

    Application.ScreenUpdating = False

    Set pfMain = ptMain.PivotFields("Date")

    bMI = pfMain.EnableMultiplePageItems

    For Each pt In wsMain.PivotTables

        If pt <> ptMain Then

            pt.ManualUpdate = True

            Set pf = pt.PivotFields("Date")

                    bMI = pfMain.EnableMultiplePageItems

                    With pf

                        .ClearAllFilters

                        Select Case bMI

                            Case False

                                .CurrentPage = pfMain.CurrentPage.Value

                            Case True

                                .CurrentPage = "(All)"

                                For Each pi In pfMain.PivotItems

                                    .PivotItems(pi.Name).Visible = pi.Visible

                                Next pi

                                .EnableMultiplePageItems = bMI

                        End Select

                    End With

                    bMI = False

           

            Set pf = Nothing

            pt.ManualUpdate = False

        End If

    Next pt

       

    Application.EnableEvents = True

    Application.ScreenUpdating = True

    End Sub

    Friday, August 10, 2012 3:51 PM
  • One conditional statement/select case is required.

    Suppose you have tables from PivotTable1 to Pivottable10.Then we need know the following.Given a sample.Update it

    Master Controller Pivot Slave Pivot
    PivotTable1 PivotTable3
    PivotTable1 PivotTable4
    PivotTable1 PivotTable5
    PivotTable2 PivotTable6
    PivotTable2 PivotTable7
    PivotTable2 PivotTable8
    PivotTable2 PivotTable9


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, August 11, 2012 6:08 AM
    Answerer