Update Pivot Table from disconnect slicers

[ertuoc]

I have created a report that uses a pivot table and multiple slicers.
I would like the users to be able to select their choices in the slicers WITHOUT having pivot table 1 auto update and then click a "Submit" button that will update the pivot table 1 all at once with the each slicer settings. Much like a web base query tool.

I have created a duplicate pivot table that uses the same source data as pivot table 1. Lets call this pivot table 2.
Pivot table 2 which is hidden and is connected to all the slicers but has no columns or rows added which allows the slicers to auto update very quicker and MUCH quicker than if the slicers were connected to pivot table 1. Thus the slicer are NOT connected to pivot table 1 and I connect the slicers to pivot table 2 because I need the cascading filtered updates of the other slicers but done quickly.

I want to take the results of the slicers after a "Submit" button is clicked and apply these to pivot table 1.
The problem I run into is slowness.

Looping through all the pivot table 1 items and deselecting and then selecting takes too long even when .ManualUpdate = true and application.calculation = xlCalculationManual is set.
Is there a way to pass all the slicer settings to pivot table 1 at once much like a variables are sent to a sql query?

Any help would be appreciated. Thanks

 

[scottsen]

I can't think of any easy/supported way to do this. You can disable pivot table refreshes via ActiveSheet.PivotTables(1).PivotCache.EnableRefresh = False... but you get this crappy dialog that pops up when you slice... telling you that refresh is disabled. Not sure if there is some way to nuke that dialog.

We could probably help make your model faster?

 

[ertuoc]

Thank You Scott but I am going to port this model to a mySql database with a web based front end which will solve my problem.
I have it in excel for now but for my users but I would just like to make it more responsive.