Macro won't run correctly when a worksheet is hidden

Jul 20, 2010
Reaction score
Hi all -

Very new to macros, and running into a bit of a snag. The idea is a macro creates a separate worksheet for each member of a team. The team members' worksheets are copies of a template, which is also a worksheet in the file

I've got this macro working the way I want it to:

Sub CreateSheetsFromAList()
   	Dim MyCell As Range, MyRange As Range
   	Set MyRange = Sheets("Season info").Range("D2:D25")
   	Set MyRange = Range(MyRange, MyRange.End(xlDown))
   	For Each MyCell In MyRange
   		If IsEmpty(MyCell.Value) Then End
   		Sheets("Template").Copy after:=Sheets(Sheets.Count) 'creates a new worksheet
   		Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
   	Next MyCell
   End Sub

However, I would like to hide the "Template" worksheet so that no end user can muck it up.

When the Template is hidden, the script generates a worksheet for the first name in the list, and renames "Template" to "Template (2)." It also makes the new worksheet hidden and gives a "Subscript out of range" error.

Any thoughts?
Last edited:

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