Refreshing pivot tables in a protected sheet

Hello all,

I have a workbook which has 2 sheets in it. One has a large amount of source data and in the other sheet I've created a set of 10 pivot tables which is fed from the source data sheet. I wanted to have the pivot tables update based upon changes to the source data using a Macro/VBA and I've found that using "ThisWorkbook.RefreshAll" works like a charm. However, I'd now like the sheet with the pivot tables to be protected from changes after the data refreshes. I'm guessing there is a better code to use in Macros/VBA but I've not been able to put my finger on it after many many web searches. Does anyone know the best code to achieve this? If anyone here has the answer to this question I'd be most appreciative!

Best,
Jenn

Hi Marvin,

Thanks so much for responding! I tried modifying my VBA code to start with unprotect, run the refresh (using ThisWorkbook.RefreshAll) and then protect but it seems that the "ThisWorkbook.RefreshAll" code is linked to both the sheets in my workbook and it didn't work to refresh the pivot tables and then it locked the source data sheet which I don't want. I want to keep the source data sheet unprotected at all times so people can enter data in it but I want to protect the pivot tables sheet so that people entering data into the source sheet don't inadvertantly modify the pivot table structure. I hope this makes sense. I have a feeling I need to use different lines of code to do this but I just can't put my finger on what the code is when Googling this scenario. Any additional help or advice would be awesome.

Thanks much
Jenn

Hi again Marv,

Thank you so much! I had not worked with Macros/VBA in many years so I completely forgot you can record a Macro and it will record the code for you.
I just did this and then created a button in my sheet which updates the data from one sheet to another and then re-protects the sheet as the final step,
and it worked like a charm!! This final feature put the finishing touches on my Excel project and I'm ready to present it to my client tomorrow! Thanks again!

Best,
Jenn