Thread: Unprotect sheets using macro

Thank you Rory I just turned off the protection for the control tab and everything works fine. I will just leave it that way cause since all the sheets are protected the client won't think I left one unprotected.

Just as an added

Sub Sheet_protection()
Dim sh As Worksheet
For Each sh In Sheets(Array("Summary-Graphs", "Summary", "YTD Rates", "Cost & Utilization", "Brand vs. Generic", _
"Drug Class", "Opioids", "Control", "Raw Data", "Raw Membership"))
sh.Unprotect
Next sh
'YOUR OTHER CODE
For Each sh In Sheets(Array("Summary-Graphs", "Summary", "YTD Rates", "Cost & Utilization", "Brand vs. Generic", _
"Drug Class", "Opioids", "Control", "Raw Data", "Raw Membership"))
sh.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
Next sh

End Sub

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

Just a quick question and I might be missing something. I started working on one other workbook that I had to do the same thing with but with different fields and sheets. Below is how I updated the macro code but I am getting and error ate

For Each varItem In varSheets
        Sheets(varItem).Unprotect Password:=cstrPASSWORD
    Next varItem

The full code is

Sub ComboBox()

    Const cstrPASSWORD As String = "password"
    Dim varSheets
    Dim varItem
    Dim wsControl As Worksheet
    Dim strRegion As String
    Dim strSpecialty As String
    Dim strGPIDesc As String
    Dim strMonthFilled As String
    Dim strTherClass As String
    
    Set wsControl = Sheets("Control")
    With wsControl
        strRegion = .Range("C1").Value
        strSpecialty = .Range("G1").Value
        strGPIDesc = .Range("K1").Value
        strMonthFilled = .Range("O1").Value
        strTherClass = .Range("S1").Value
    End With

    varSheets = Array("Summary-Graphs", "RX Cost-Plan", "Amount Paid-Region", "Rx Cost - Region", "Specialty RX-Region", _
                      "High Cost Drugs-Region(1)", "High Cost Drugs-Region(2)", "Pivot Table", "Raw Data", "Region Elig", "Plan Elig")

    For Each varItem In varSheets
        Sheets(varItem).Unprotect Password:=cstrPASSWORD
    Next varItem



   
 With Sheets("Rx Cost - Region")
        With .PivotTables("PivotTable1")
            .PivotFields("Region").CurrentPage = strRegion
        End With
        With .PivotTables("PivotTable2")
            .PivotFields("Region").CurrentPage = strRegion
        End With
    End With

    With Sheets("Specialty RX-Region")
    With .PivotTables("PivotTable3")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("Specialty").CurrentPage = strSpecialty
    End With
    With .PivotTables("PivotTable4")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("Specialty").CurrentPage = strSpecialty
    End With
End With

    With Sheets("High Cost Drugs-Region(1)").PivotTables("PivotTable5")
        .PivotFields("Region").CurrentPage = strRegion
    End With

    With Sheets("High Cost Drugs-Region(2)")
      With .PivotTables("PivotTable6")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("GPIDesc").CurrentPage = strGPIDesc
        .PivotFields("MonthFilled").CurrentPage = strMonthFilled
      End With
      With .PivotTables("PivotTable7")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("GPIDesc").CurrentPage = strGPIDesc
        .PivotFields("MonthFilled").CurrentPage = strMonthFilled
      End With
      With .PivotTables("PivotTable8")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("GPIDesc").CurrentPage = strGPIDesc
        .PivotFields("MonthFilled").CurrentPage = strMonthFilled
      End With
    End With

    With Sheets("Pivot Table").PivotTables("PivotTable9")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("Specialty").CurrentPage = strSpecialty
            .PivotFields("TherClass").CurrentPage = strTherClass
        End With

        

    For Each varItem In varSheets
        Sheets(varItem).Protect Password:=cstrPASSWORD, DrawingObjects:=True, Contents:=True, Scenarios:=True _
                              , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
                              , AllowUsingPivotTables:=True
    Next varItem

    Sheets("Filter Selection").Select

 
End Sub

Run-time error '9':

Subscript out of range

I found the problem I accidently added spaces where there shouldn't have been in some of my sheet names.