Results 1 to 5 of 5

Help on VBA - change pivot filter fields

Threaded View

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Switzerland
    MS-Off Ver
    MS Excel 365
    Posts
    68

    Help on VBA - change pivot filter fields

    Hi!

    I am using the following VBA to transfer the pivot filters to other tables. This code is designed to handle/transfer one single pivot filter field, but I need 3 fields.

    I have another VBA which changes all fields but due to performance issues not an option. Slicers are not an option as well.

    How can I amend this VBA to handle 3 fields?

    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
    
    'change only Region field for all pivot tables on active sheet
    
    Set pfMain = ptMain.PivotFields("Region")
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
        If pt <> ptMain Then
            pt.ManualUpdate = True
            Set pf = pt.PivotFields("Region")
                    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
    Last edited by FixandFoxi; 01-24-2017 at 08:58 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot Table missing filter drop down for Row Fields
    By lora2014 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-19-2018, 10:37 AM
  2. [SOLVED] Pivot Table still has old values in filter fields
    By Joshua H in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-23-2016, 11:19 AM
  3. Looping through in Pivot table Filter fields
    By zsirsaman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2016, 09:31 AM
  4. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM
  5. VBA to change filter for n pivots based on page filter selection for the first pivot
    By shama.arige in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2011, 11:42 AM
  6. Pivot Table filter with added fields?
    By rbpd5015 in forum Excel General
    Replies: 3
    Last Post: 08-17-2010, 02:10 PM
  7. [SOLVED] How do I set up filter for page fields in pivot table?
    By Mitsycat in forum Excel General
    Replies: 3
    Last Post: 05-06-2005, 06:06 PM

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