How can I change this worksheet code to a button hit?

[JJBennett]

I found some code on the Contextures website that takes pivot table selections from one worksheet, and updates the pivot tables on all other worksheets to match. It's not tied to a control - it is active on the worksheet, so that each time a filter is updated, it automatically writes to the others.

It works great - but I also have a macro on my page that loops - and it's causing this code to take a long time to process. For each loop through my macro, this one keeps updating everything across the workbook.

So - how can I change this so that it can be run via button hit, instead of being always active on the worksheet?

Thanks!

Please Login or Register  to view this content.

[AlphaFrog]

Move the code from the worksheet's code module to a standard code module e.g. Module1

Change the name of the macro to something like; Sub Main_PivotTable_Update()

Change these two lines in the macro.
Set wsMain = Sheets("Main")
Set ptMain = wsMain.PivotTables(1)

Change the name of the sheet and index# of the main pivot table to suit.

Assign this macro to a command button.

Note; this is not tested.