Screen 'Flashing' when refreshing Pivot Tables

I have a worksheet which contains a number of pivot tables for use as a dashboard. The actual pivots themselves are all hidden but their output shown in different cells on the sheet. I want the pivots to all automatically update when the user changes data on another sheet, which I am doing using this code. The code does the job but the dashboard sheet looks a bit 'messy' when updating all the pivots, by that I mean the refresh for each pivot makes the screen redraw and it just doesn't look so great. Is there any way to adapt the code so that this doesn't happen or only happens once rather than for each individual pivot refresh so it looks much smoother?

I can't easily upload a sample because the data is sensitive and has taken many many hours to create. I think it is simply an issue of screen redraw/refresh.

Please Login or Register  to view this content.

Many thanks

A million times better, works like a charm...

Many thanks for your help

Actually, one further thought. Ideally I want the sheet in question to be protected but of course it won't refresh the pivots when it is protected. Is there a way to further adapt the code to unprotect the sheet whilst the refresh is happening and then to protect it again once refreshed?

Not sure how that would work in practice?

Okay, now I'm confused, added the code ActiveSheet.Protect "YourPasswordHere" before Application.ScreenUpdating = False and then ActiveSheet.UnProtect "YourPasswordHere" after Application.ScreenUpdating = True.

The sheet doesn't actually have a password, so simply used "" but I got an error, so tried adding a password 'pass', still had the error, commented out all the code and now the sheet is asking for a password to unlock the sheet and whatever I enter, either nothing or 'pass', I now have the message 'The password you supplied is not correct and subsequently I can now not unlock the sheet.

Any idea how I can get round this problem first and then, am I adding the Protect and Unprotect in the correct place?

Okay, perfect, that did the trick, thank you...

Is there any way to achieve the same thing when using 'slicers' on the sheet itself. Every time a different button is selected on the slicer the same thing is effectively happening in that the screen refreshes giving a similar 'issue'?

Good point, I guess there is no way around that one, not the end of the world, I guess I'll just have to live with it...

Many thanks for all your help once again...

Um,

So, I thought this was working but there is a problem. There are other sheets containing pivot tables using the same source data, so I now get a constant error which says:

Run-time error '1004'
That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data.

I've assumed it is referring to other portected sheets in the workbook, but even unprtecting every sheet in the workbook, the problem still exists, so unsure if it is referring to the other sheets or other PivotTables on the same sheet?

Please Login or Register  to view this content.

Any ideas?

I can't attach the actual workbook due to confidentiality, but I've managed to quickly recreate the problem using the attached. Basically it appears that if you have a pivot table that uses the same source data on another protected sheet then that is causing the issue.

On the attached I've added four PivotTables to the tab called Pivot and added the code to 'UnProtect, Refresh the Pivots and then Protect' the sheet again. On it's own that all works fine but then I've added a second sheet called More Pivots, added a PivotTable there using the same data source, protected that sheet and that's when the problem starts.

If you now simply click once on the Data tab you will see the run-time error. If you then UnProtect the sheet called More Pivots the problem goes away. What is odd though is that on my main sheet, I've UnProtected ALL the sheets and I'm having the porblem, so I don't know whether Excel somehow stores details of previously deleted sheets or something, but I really need to be able to Protect some of the other sheets in the Workbook.