Need a faster way to sync pivot table filters in Excel 2010

Hi All

I have two pivot tables where i sync the filters as in the code below. Can somebody perhaps help me with a more effective/ faster way to get the same results?

I do set calculations to manual and disable events in the sub that calls this one.

Sub SinqPivotTableSupplierProfile()



Dim strFilterValue As String
Dim ptUpdate As PivotTable
Dim i As Long
Dim Pvi As PivotItem
Dim dic
Dim strSheetName As String
Dim strFilter() As String
Dim pt As PivotTable

Set pt = Sheets("Responses").PivotTables("PivotTable69")

Set dic = CreateObject("scripting.dictionary")
i = 0

'Find all pivot item names that are visible, add to array of visible items
    For Each Pvi In pt.PivotFields("Supplier").PivotItems
           If Pvi.Visible = True Then
                dic.Add Pvi.Name, i
                i = i + 1
           End If
    Next Pvi

'Loops through non target pivot tables and applies the filter to the supplier field
        For Each ptUpdate In Sheets("Summary").PivotTables
            If ptUpdate.Name = "SupplierProfiles" Then
                For Each Pvi In ptUpdate.PivotFields("Supplier").PivotItems
                    If dic.exists(Pvi.Name) Then
                       Pvi.Visible = True
                    End If
                Next Pvi

                For Each Pvi In ptUpdate.PivotFields("Supplier").PivotItems
                    If Not dic.exists(Pvi.Name) Then
                       Pvi.Visible = False
                    End If
                Next Pvi
            End If
        Next ptUpdate
        
End Sub

Open in new window

LVL 1
cErasmusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rgonzo1971Commented:
Hi,

pls try

Sub SinqPivotTableSupplierProfile()

Application.ScreenUpdating = False

Dim strFilterValue As String
Dim ptUpdate As PivotTable
Dim i As Long
Dim Pvi As PivotItem
Dim dic
Dim strSheetName As String
Dim strFilter() As String
Dim pt As PivotTable, pt2 As PivotTable

Set pt = Sheets("Responses").PivotTables("PivotTable69")

Set dic = CreateObject("scripting.dictionary")
i = 0

'Find all pivot item names that are visible, add to array of visible items
    For Each Pvi In pt.PivotFields("Supplier").PivotItems
           If Pvi.Visible = True Then
                dic.Add Pvi.Name, i
                i = i + 1
           End If
    Next Pvi


    Set pt2 = Sheets("Summary").PivotTables("SupplierProfiles")
    For Each Pvi In pt2.PivotFields("Supplier").PivotItems
       If dic.Exists(Pvi.Name) Then
          Pvi.Visible = True
       Else
          Pvi.Visible = False
       End If
    Next Pvi


Application.ScreenUpdating = True

End Sub

Open in new window

Regards
0
 
cErasmusAuthor Commented:
Hi Rgonzo1971

Thank you for the reply. I have tried the code you supplied but the running time is almost exactly the same. with the old code being about 0.5 of a second faster. I have no idea why this is.
I did have screen updating set to false previously as well.
Do you perhaps have any other ideas on how i can sync the filters faster?
0
 
Rgonzo1971Commented:
HI,

Instead of a dictionary you could try an array ( even if I do not think it will make a huge dîfference)

Sub SinqPivotTableSupplierProfile()

Application.ScreenUpdating = False

Dim strFilterValue As String
Dim ptUpdate As PivotTable
Dim i As Long
Dim Pvi As PivotItem
Dim dic
Dim strSheetName As String
Dim strFilter() As String
Dim pt As PivotTable, pt2 As PivotTable
Dim arr()
Set pt = Sheets("Responses").PivotTables("PivotTable69")
ReDim arr(0 To pt.PivotFields("Supplier").PivotItems.Count - 1)
'Set dic = CreateObject("scripting.dictionary")
i = 0

'Find all pivot item names that are visible, add to array of visible items
    For Each Pvi In pt.PivotFields("Supplier").PivotItems
           If Pvi.Visible = True Then
                arr(i) = Pvi.Name
                i = i + 1
           End If
    Next Pvi


    Set pt2 = Sheets("Summary").PivotTables("SupplierProfiles")
    For Each Pvi In pt2.PivotFields("Supplier").PivotItems
    IsInArray = Not IsError(Application.Match(Pvi.Name, arr, 0))
       If IsInArray Then
          Pvi.Visible = True
       Else
          Pvi.Visible = False
       End If
    Next Pvi


Application.ScreenUpdating = True

End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
cErasmusAuthor Commented:
Hi Rgonzo1971

You're right it doesn't make much of a difference. i have spent quite some time on finding the fastest way possible to sync filters and this has been the fastest way i could do it. I was hoping there might be another way but it doesn't seem like it. Thank you for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.