Macro with variable tab names

[Will85]

I have a a number of workbooks that each have 19 tabs. I have a macro that goes through each tab and hides some blank rows.

My current macro, as an example for one tab is as follows:

Sheets("1000").Select
ActiveSheet.Unprotect
ActiveSheet.Range("$A$1:$A$700").AutoFilter Field:=1, Criteria1:="1"
ActiveSheet.Protect

The sheet name "1000" is only applicable in one workbook, in another workbook the number scheme may change to "6001" as an example.

I would rather have my macro refer to a key to lookup the tab name, than actually hardcode the tab names in the macro.

For example, I have a tab called "Departments", and cells A1:A19 contain the department code that also corresponds to the tab names in each of the workbooks. I would prefer the macro pull the tab names from here, so that when I copy this workbook out I can change the "Departments" tab instead of the macro.

Thank you in advance for your help.

 

[Fluff]

How about

Sub hiderws()
   Dim ary As Variant
   Dim sht As Variant
   
   ary = Sheets("Departments").Range("A1:A19").Value
   For Each sht In ary
      With Sheets(CStr(sht))
         .Unprotect
         .Range("A1:A700").AutoFilter 1, "1"
         .Protect
      End With
   Next sht
End Sub

 

[Will85]

Error @ With Sheets(CStr(sht))

 

[Fluff]

Do all cells in A1:A19 contain valid sheet names?

 

[Will85]

Im realizing that it worked, but its throwing that error.

Run-time error 9

Subscript out of range

With Sheets(CStr(sht)) is the highlighted code

 

[Will85]

I realized what was happening, while testing it, I created a blank workbook, with one valid tab, and only put one value in cell A1 on the "Departments tab"

I think I will make the A1:A19 relative just in case in the future a user adds or deletes rows, its conceivable we could add an additional department. Thank you for your assistance.

 

[Fluff]

You can make the list dynamic like

Sub hiderws()
   Dim ary As Variant
   Dim sht As Variant
   
   With Sheets("Departments")
      ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
   End With
   For Each sht In ary
      With Sheets(CStr(sht))
         .Unprotect
         .Range("A1:A700").AutoFilter 1, "1"
         .Protect
      End With
   Next sht
End Sub

 

[Will85]

At the end of the macro, after going through all sheets within the variable range. I want it to end by selecting the "Instructions" sheet (which is where I have the button that runs the macro)

I am struggling with this as I keep getting exceptions when I try to add Sheets("Instructions").Select

 

[Fluff]

That should work as long as you add it between these 2 lines

   Next sht
End Sub

 

[Will85]

At the end of the macro, after going through all sheets within the variable range. I want it to end by selecting the "Instructions" sheet (which is where I have the button that runs the macro)

I am struggling with this as I keep getting exceptions when I try to add Sheets("Instructions").Select