Event refresh Pivot

[mrcoffee]

I'm having trouble with using workbook events to fire a simple macro that deletes a couple of worksheets and refreshes a pivot table.

The scenario is that an accounting application I'm using spools reports into excel, specifically onto a template that i've setup to include a pivot table with a dynamic named range. THe application outputs rows of data onto one sheet i'm then using the workbook close event to trigger a macro which (should!) refresh the pivot table and delete some unneeded sheets. I know the macro fires because the unneeded sheets are deleted but the pivot table doesn't refresh.
Using exactly the same code outside of the scenario of the accounting application it works perfectly.

A) What's going on?

B) More importantly what can I do about it? is there another way to refresh the pivot table other than the code i'm using that might work?

Please Login or Register  to view this content.

As I say the code does work, its just not refreshing the pivot table specifically when the accounting application works with it - I can take the output report, open and close it and the pivot table will refresh, though as I say the macro is firing because the worksheets get deleted.

[jrussell]

If you're using Excel 2007, this works:

Please Login or Register  to view this content.

If you're using 2003 or earlier:

Please Login or Register  to view this content.