Multiple Worksheets Protection

  • Thread starter Leslie Williams
  • Start date


Leslie Williams

I have 75+ worksheets that need to be protected and unprotected at the same
time. I have used a macro to achieve this process, however I now have
created a pivot table to narrow the information into a separate report. One
of the worksheets is a summay of all data from each sheet, and this
particular worksheet is the source for my pivot table. My problem is that
when I use the protect all macro, the pivot table is included and no changes
can be made. This table basically has one field that requires the user to
make a selection from a drop down cell and the report is then updated with
the information. Is there any way to get the macro to protect a range of
worksheets that does not include the pivot table sheet? I am currently using
Office 2007 but others using this spreadsheet have older versions of Office




always helpful if you include copy of the macro in question on this board.

See if following helps you - change PivotSheetName as required.

Sub ProtectSheets()
Dim sh As Variant
For Each sh In ThisWorkbook.Sheets

If sh.Name <> "PivotSheetName" Then sh.Protect

Next sh

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question