Hi all,
I have two sheets that have pivot tables on them. I want the data to update when the workbook is opened. The sheets need to otherwise be protected. When I try and update with protection on, it error-messages me. I've tried putting the following code in "ThisWorkbook":
As far as I can tell, this should unprotect the relevant sheets (the two that get unprotected are the only ones with pivot tables on them - all feed off the first pivot table that is on 'analysis'), update the data, and re-protect the sheets. However, when I do it, I get the same message that I would when I have one sheet unprotected and the other protected, and I try to update the data on the unprotected sheet . Incidentally, it does follow the last command - to go to the 'Intro' sheet.
Any ideas???
I have two sheets that have pivot tables on them. I want the data to update when the workbook is opened. The sheets need to otherwise be protected. When I try and update with protection on, it error-messages me. I've tried putting the following code in "ThisWorkbook":
Code:
Private Sub Workbook_Open()
' Unprotect sheets
Sheets("Analysis").Select
ActiveSheet.Unprotect "password"
Sheets("Commission Information").Select
ActiveSheet.Unprotect "password"
' Update pivot info
ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen = True
' Protect sheets and finish on intro
ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
Sheets("Analysis").Select
ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
Sheets("Intro").Select
End Sub
As far as I can tell, this should unprotect the relevant sheets (the two that get unprotected are the only ones with pivot tables on them - all feed off the first pivot table that is on 'analysis'), update the data, and re-protect the sheets. However, when I do it, I get the same message that I would when I have one sheet unprotected and the other protected, and I try to update the data on the unprotected sheet . Incidentally, it does follow the last command - to go to the 'Intro' sheet.
Any ideas???