Hi,
I am trying to create a macro which will filter a large pivot table for a set of specified accounts. The pivot table contains accounts for multiple sales staff (total number accounts approx 50k). For sales people to see only certain accounts they need to manually select them in the pivot filter but this is a slow process for them (also error prone as they may miss an account or select the wrong one). The idea is that they can paste a list of the account id's into another sheet and run a macro which takes these accounts and filters the pivot table only to show them.
The good thing is that this works but it is really really slow. When I stepped through the code I see that the loop I set up is going through the pivot table account by account and either selecting it or de-selecting it and then it refreshes the pivot table each time. This takes forever because it has to go through the full 50k accounts.
I assumed there must be a way to stop the pivot table from refreshing until the loop has finished going through all the accounts. I came across this bit of code:
which seems to turn off the automatic pivot table updating but it doesn’t seem to have any noticeable impact on the speed of the macro.
I decided to record a manual selection of accounts and I was surprised to see how Excel handles it:
This continues for every account in the table and would explain why the macro is so slow to run. The loop is effectively doing the same thing.
I know that if I want to do a similar thing with an auto filter and I record it then it will look something like this:
So here it uses an array to select what I want to see rather than going through every single account and setting it to visible or invisible.
Is there any way to use this approach with a pivot table or are the pivot filters handled in a different way?
Sorry if this is a long and rambling post but I am trying to give as much information as I can. Is this just a VBA limitation or is there a way around this?
Thanks!
Bookmarks