none
changing Pivot filter on different sheets, different data source RRS feed

  • Question

  • Hello,

    Let me start off with -I am learning VBA and currently my knowledge is limited to recording Macros and tweaking them to improve performance. I have a excel file with separate DB data queries on different tabs with results as pivot tables- the common factor on all tabs is Billing Group filter. I have a Billing Group  drop down on tab "T" from a data validation list. I need to set the filter on all pivots on all tabs to be linked to the same drop down. Since the data source is not the same cannot use slicers. Tried using below code but it doesn't work. Please help!

    Option Explicit
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    SyncPivotsAnyVersion Target
    End Sub

    Thursday, August 17, 2017 1:55 PM

All replies

  • Hello,

    Are these pivot tables in the same sheet or different sheets? What is the result of the current code?

    What code do you use in SyncPivotsAnyVersion?

    I suggest you share a sample file here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 18, 2017 6:45 AM
    Moderator
  • Hello Celeste,

    The Pivot table is on a different sheet than the parameter. I have 4 data queries on 4 different tabs with results as pivot tables on each tab.

    The above code is did not have any effect- Here is a link to a sample file where I got the code from.

    http://www.contextures.com/excelfiles.html

    PT0029 - Change Pivot Table Fields on Specific Sheets -- Change any page field in a pivot table, and the same selections are made in all other pivot tables that contain that page field. Specify which worksheets to change, and which pivot tables and pivot fields to ignore. Uses Slicers, if version is Excel 2010 or later. Sample code from Jeff Weir. PivotMultiPagesChange_JW.zip 45 kb 27-Aug-12   Updated 21-Jun-13

    Tuesday, August 22, 2017 1:01 PM
  • Hello,

    Do you handle PivotTableUpdate event for all sheets? 

    Hi wandering,

    >> I need to set the filter on all pivots on all tabs to be linked to the same drop down.

    Based on this, it seems you want to use one drop down to filter all the pivots, if so, I think you need to loop through the PivotTables and filter them, I suggest you try to check the code in below link

    # VBA to Autofilter Pivot Tables on change via combobox

    https://social.msdn.microsoft.com/Forums/office/en-US/cedabcf0-fb86-4331-952c-4b73332fbdf4/vba-to-autofilter-pivot-tables-on-change-via-combobox?forum=exceldev

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, August 23, 2017 7:05 AM
    Moderator
  • Thanks Edward, Celeste. I tried using the code in the link and it runs fine but doesnt change the pivot. :(
    Thursday, August 24, 2017 4:44 PM
  • Hi wandering_angel,

    Could you share us a demo workbook and detail steps to reproduce your issue?

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, August 25, 2017 1:34 AM