+ Reply to Thread
Results 1 to 3 of 3

Change Report Filters for two pivot tables on the Same Sheet

  1. #1
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Change Report Filters for two pivot tables on the Same Sheet

    First let me apologize if i don't know how to paste a VBA code within the forum specifications.

    Found this code on the internet which updates ALL pivot tables in the workbook with the same report filters. What should i do to modify this code to only update two pivot tables on the same worksheet:


    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    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

    For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
    If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(pfMain.Name)
    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
    Next ws
    Next pfMain

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

    Please help. I've tried modifying the code with my little VBA knowledge but i can't get it to work.
    If I've helped U pls click on d *Add Reputation

  2. #2
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Change Report Filters for two pivot tables on the Same Sheet

    finally got it to work. just needed to delete a few lines.
    Last edited by bhenlee; 07-29-2015 at 04:28 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Change Report Filters for two pivot tables on the Same Sheet

    wish i could rep myself LOL!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Filter report filters based upon previous report filters!!??
    By jackie_m in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-21-2015, 05:17 AM
  2. Pivot Filters to change simulataneously with other pivot filters
    By ScottBeatty in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 07-02-2014, 10:48 AM
  3. Locking only one page field (report filters) of a pivot table in a protected sheet???
    By kzahariev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 08:48 AM
  4. Pivot Table with 2 Report Filters.
    By DSL1970 in forum Excel General
    Replies: 1
    Last Post: 04-16-2013, 07:17 PM
  5. Multiple Report Filters in Pivot Table
    By ramjet in forum Excel General
    Replies: 0
    Last Post: 07-12-2012, 01:45 PM
  6. Change Report Filter Multiple Pivot Tables
    By jpedges38 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2012, 11:07 AM
  7. Pivot tables - Multiple filters across 2 tables
    By shoesterix in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2011, 05:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1