crvazquez954
New Member
- Joined
- Jul 9, 2019
- Messages
- 23
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
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