Solved

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

Posted on 2014-04-03
4
591 Views
Last Modified: 2014-04-03
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

0
Comment
Question by:cErasmus
  • 2
  • 2
4 Comments
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 39974453
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
 
LVL 1

Author Comment

by:cErasmus
ID: 39974721
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
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39974768
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
 
LVL 1

Author Comment

by:cErasmus
ID: 39974804
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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question