MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 80|Reply: 0

Help on VBA - change pivot filter fields

[Copy link]

1

Topics

1

Posts

7

Integral

Newbie

Rank: 1

Integral
7
Post on 1-13-2018 05:19:55 | All posts |Read mode


Hi!
I am using the following VBA to transfer the pivotfilters to other tables. The pivot filters are same. But the items in masterfile is less (10) than the items in the secondary pivot tables.  

When I to select multiple items from pivot fields in the masterpivot table, only some of the pivot items in the secondary pivot get selected (allthough selected items are available in both pivot tables.)

How can I amend this VBA to select multiple items in the pivot filtersin both master and secondary pivot tables.


OptionExplicit

Private SubWorksheet_PivotTableUpdate(ByVal Target As PivotTable)

On ErrorResume Next

Dim wsMainAs Worksheet

Dim ws AsWorksheet

Dim ptMainAs PivotTable

Dim pt AsPivotTable

Dim pfMainAs PivotField

Dim pf AsPivotField

Dim pi AsPivotItem

Dim bMI AsBoolean


On ErrorResume Next

Set wsMain =ActiveSheet

Set ptMain =Target


Application.EnableEvents= False

Application.ScreenUpdating= False


For EachpfMain 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 piIn 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





Reply

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 2-22-2018 23:03 , Processed in 0.344719 second(s), 21 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!