create specific tabs if they're not already in the file

[SandsB]

My file could have about 30 tabs in it, one for each volunteer. If a volunteer didn't work on a particular day there's no tab for him. That's always been OK but now I need to add a tab that looks at a list of volunteers and if a person doesn't already have a tab in the file I need a blank tab created, named for the volunteer, with just the word "OFF" in A1.
The file already has a list of all the volunteers in column A of a tab named Vol. so the macro could look at the list to see what tabs already exist. The number of volunteers changes all the time so the number of people in the list changes, too.

So - if today 28 volunteers worked the file already has 29 tabs in it. 28 named for each of the volunteers and one named Vol with the names of all volunteers. Bob Jones and John Smith took the day off. I need a macro that will create a tab named Bob Jones and and other tab named John Smith and put the word OFF in A1 of each of those tabs.

 

[Fluff]

How about

Sub SandsB()
   Dim Cl As Range
   With Sheets("Vol")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("Isref('" & Cl.Value & "'!a1)") Then
            Sheets.Add(, Sheets.Count).Name = Cl.Value
            Range("A1").Value = "OFF"
         End If
      Next Cl
   End With
End Sub

 

[SandsB]

Sheets.Add(, Sheets.Count).Name = Cl.Value

This line gives me an error I'm not sure about:

Run-time error '1004'
Method 'Add' of object "Sheets" failed

 

[Fluff]

Oops, that line should be

            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value

 

[SandsB]

Brilliant!! Thank you Fluff.

 

[mayday1]

Stupid user question. This will save me a lot of time.

How could this be made to work if the Vol tab was in a separate file named MyFile.xlsx?

 

[Fluff]

Brilliant!! Thank you Fluff.

You're welcome & thanks for the feedback

 

[Fluff]

@mayday1
Like

   Dim Cl As Range
   
   ThisWorkbook.Activate
   With Workbooks("Book1.xlsm").Sheets("Vol")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("isref('" & Cl.Value & "'!a1)") Then
            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
            Range("A1").Value = "OFF"
         End If
      Next Cl
   End With

 

[mayday1]

Sweet!

 

[Fluff]

You're welcome & thanks for the feedback