I am working on a file with about 20 sheets of pivot tables & slicers. Is there a way for me to protect/unprotect all of the sheets at the same time while allowing the use of the slicers? When I use the macro below, it protects the sheets at the same time but then the slicers no longer work. They work when I protect the sheets one at a time though. Please help as it's a nightmare each time I have to make any changes. Thanks!
Sub Protect_Sheets()
' Loop through all sheets in the workbook
For I = 1 To Sheets.Count
' Activate each sheet in turn.
Sheets(I).Activate
response = MsgBox("Do you want to protect this sheet?", vbYesNo)
If response = vbYes Then
ActiveSheet.Protect Password:="****", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ElseIf response = vbNo Then
MsgBox ("Sheet not protected")
End If
Next I
End Sub
' Loop through all sheets in the workbook
For I = 1 To Sheets.Count
' Activate each sheet in turn.
Sheets(I).Activate
response = MsgBox("Do you want to protect this sheet?", vbYesNo)
If response = vbYes Then
ActiveSheet.Protect Password:="****", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ElseIf response = vbNo Then
MsgBox ("Sheet not protected")
End If
Next I
End Sub