Opening worksheets using worksheet names in a named range

[anythinggoes3949]

Hello, I've been writing fairly simple macros for a few years and can usually find what I need in the forums. This time though I'm completely stumped.

I have workbook containing multiple worksheets. This includes a a worksheet called "Master" which contains a list of a select few of the worksheet names found within the same workbook. I've set this list as a named range called "Worksheet_Names".

I would like the macro to loop through the list of worksheet names, open each worksheet in the list in turn and password protect these worksheets. I don't want to password protect every sheet in the workbook, only those listed within this range. If I can get this to work, then I'll set up another macro to unprotect the same sheets.

I'm finding the code gets stuck (debug error) at the 'Sheets(Range(" & cell & ").Value).Activate' line of code so I'm assuming I've done something wrong here. Any help would be greatly appreciated. This is the first time I've posted here so apologies if I've done anything wrong within my post.

Sub Protect_Sheets()
'
Sheets("Master").Activate
Dim cell As Range
For Each cell In ActiveSheet.Range("Worksheet_Names")
Sheets(Range(" & cell & ").Value).Activate
ActiveSheet.Protect "Password1234"
Next cell

End Sub

 

[Kaper]

This shall do the job:

sub do_protect()
Dim cell As Range
For Each cell In Sheets("Master").Range("Worksheet_Names")
  Sheets(cell.Value).Protect "Password1234"
Next cell
end sub

Note no activation of sheet is called. It would make screen flicker. If for some reason you need it then consuder adding

Application.ScreenUpdating = False

at the beginning of the code and switch it to True at the end of the code

 

[PeteWright]

Try this:

Sub Protect_Sheets()
   Dim Worksheet_Names As Range
   Set Worksheet_Names = Sheets("Master").Range("Worksheet_Names")
   Dim cell As Range
   For Each cell In Worksheet_Names
      Sheets(cell.Value).Protect "Password1234"
   Next cell
End Sub

 

[anythinggoes3949]

This shall do the job:

sub do_protect()
Dim cell As Range
For Each cell In Sheets("Master").Range("Worksheet_Names")
  Sheets(cell.Value).Protect "Password1234"
Next cell
end sub

Note no activation of sheet is called. It would make screen flicker. If for some reason you need it then consuder adding

Application.ScreenUpdating = False

at the beginning of the code and switch it to True at the end of the code

I'll give this a try, thank you!

 

[anythinggoes3949]

Try this:

Sub Protect_Sheets()
   Dim Worksheet_Names As Range
   Set Worksheet_Names = Sheets("Master").Range("Worksheet_Names")
   Dim cell As Range
   For Each cell In Worksheet_Names
      Sheets(cell.Value).Protect "Password1234"
   Next cell
End Sub

I'll give this a try, thank you!