Optimising VBA Script - Changing Fields On A Pivot Table

[Lazar1us]

Hi All,

One of the Excel ninjas here a VBA script that allows me to change a filter on several pivot tables at once by inputting a value on a cell. Problem is, I don't know how to optimise it as every time I ask it to update a pivot table, it clears the values on the filter, changes it to the value I want and refreshes everything. Multiply this by even 10 pivot tables and you have yourself a script that takes up a lot of memory and time.

Would love your thoughts on how to optimise this so that it runs faster. Here's the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
            If Intersect(Target, Range("C9:C10")) Is Nothing Then Exit Sub

            Dim pt As PivotTable
            Dim Field As PivotField
            Dim NewCat As String

            Set pt = Worksheets("Pivots").PivotTables("PivotTable2")
            Set Field = pt.PivotFields("Debtor Name")
            NewCat = Worksheets("Benchmark Tables").Range("C9").Value
         
            With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 pt.RefreshTable
            End With
               
            Set pt = Worksheets("Pivots").PivotTables("PivotTable5")
            Set Field = pt.PivotFields("Debtor Name")
            NewCat = Worksheets("Benchmark Tables").Range("C9").Value

'           This updates and refreshes the PIVOT table
             With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 pt.RefreshTable
            End With
         
            Set pt = Worksheets("Pivots").PivotTables("PivotTable8")
            Set Field = pt.PivotFields("Debtor Name")
            NewCat = Worksheets("Benchmark Tables").Range("C9").Value

'           This updates and refreshes the PIVOT table
             With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 pt.RefreshTable
            End With
End Sub

 

[jeffreyweir]

Get rid of the line pt.RefreshTable as there's no need for it. (You would only need that if new data was getting added to the PivotTable).

What is the layout/orientation of the fields you are filtering? Are they in the ROWS area? Or the COLUMNS area? Or the FILTERS area?

If they are in the FILTERS area, then you can also get rid of the line Field.ClearAllFilters

Also, check out my article at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/ as it has perhaps a simpler approach you can use, if you have Excel 2010 or later.

 

[Lazar1us]

Hi jeffreyweir,

The fields that I am filtering are in the FILTERS area. This solution worked perfectly alongside adding new SubProcedure and callling it several times accordintly.

Unfortunately work is still stuck at using Excel 2007

That said, thank you all the same! This has been a huge timesaver!

 

[Lazar1us]

One more question if you don't mind, Is there a way to put in multiple values in the one cell for the above script?

Say if I have a FILTERS field that contains: FRUITS, VEGETABLES, DRINKS

what do I input into Cell C9 for the Pivot table to account for both FRUITS and VEGETABLES?

 

[jeffreyweir]

In that case you can use a version of the trick I outlined in that link above, but instead of using slicers to sync the dropdown (which requires Excel 2010 or later), you use some code.

I have some code that I'm in the middle of refreshing that will do the trick. Let me see if I can get it working and post back here.

It would also help if you could share your workbook with me. If this is an option, email me at weir.jeff@gmail.com

 

[Lazar1us]

I'd love to see the code Jeffrey - let me see if I can create a cleaned version of the worksheet so that you know what I'm doing.

 

[Lazar1us]

Hi Jeffery, Unfortunately I can't provide a sample as cleaning up the worksheets out of confidential data breaks the formulas of my predecessor. Apologies for this.

I do hope that you can still provide the code for that though - that would definitely be interesting.