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
Hot Search: Vlookup Match VBA
View: 80|Reply: 0

Help on VBA - change pivot filter fields

[Copy link]








Rank: 1

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

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.


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


                            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


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!