Excel VBA to Create New tab Templates

[crvazquez954]

I have the code below which copies a template onto a new tab whose name is taken from a list on the summary page. I would like to revise it so that when the macro is ran again it will create a tab for any NEW items on the list. I'm sure it's a simple if statement but I can't seem to figure it out. Any help is greatly appreciated.

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Summary")
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Template").Visible = True
For Each c In sh2.Range("B37", sh2.Cells(Rows.Count, 2).End(xlUp))
If Evaluate("isref('" & c.Value & "'!A1)") Then
MsgBox "Error: " & c.Value & " is a duplicate unit type. Please reset worksheet and provide unique name for each unit type"
ActiveWorkbook.Sheets("Template").Visible = False
Sheets("Summary").Select
Exit Sub
End If
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Next
ActiveWorkbook.Sheets("Template").Visible = False
Sheets("Summary").Select
Application.ScreenUpdating = True
End Sub

 

[ExcelGzh]

It seems to me that you are already checking for the existence of a sheet with the Evaluate function. What's happening is that if a duplicate is found the whole process stops. What you want to do is just skip over the ones that already exist and keep going through the list.

This variation to your code only creates a new sheet if it doesn't exist.

For Each c In sh2.Range("B37", sh2.Cells(Rows.Count, 2).End(xlUp))
  If Not Evaluate("isref('" & c.Value & "'!A1)") Then
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = c.Value
  End If
Next

 

[crvazquez954]

Thanks a bunch. That did the trick. However, the other issue I am having is when there is a blank cell. Do you have any suggestions on how I can tweak this code so that it creates a template for every unique name listed in B37:B76 while skipping anyone's that are blank?

 

[ExcelGzh]

You would need to put in a check to see if a particular cell was blank

For Each c In sh2.Range("B37:B76", sh2.Cells(Rows.Count, 2).End(xlUp))
  If Len(c)>0 Then
    If Not Evaluate("isref('" & c.Value & "'!A1)") Then
      sh1.Copy After:=Sheets(Sheets.Count)
      ActiveSheet.Name = c.Value
    End If'Not Evaluate
  End If'Len(c)>0
Next

 

[crvazquez954]

You are awesome!!! That works perfectly. Thanks a million.

 

[ExcelGzh]

Glad I could help