Create a single worksheet from a template

  • Thread starter Thread starter Shaz
  • Start date Start date
S

Shaz

Hello
The macro below (pinched from one of the threads) creates dulplicate
worksheets from a list and has served me well over a year.
However I now want to export it to another workbook starting at cell
N4.
I have tried adjusting it accordingly but am failing miserably not
least because it doesn't work with only one name or has any error
trapping when there isn't any names. Can you help.
TIA
Shaz

Sub CreateSheets()
Application.ScreenUpdating = False
For Each cell In Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants)
Dim SName As String
SName = cell.Value
If SheetExists(SName) = False Then
On Error Resume Next
Sheets("Template").Copy Before:=Sheets("Template")
ActiveSheet.Name = SName
On Error GoTo 0
End If
Next cell
Sheets("Main").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub
 
You chopped off your function SheetExists in your post. If you didn't copy it
to the new workbook, then that could be a problem.

But I think this may be what you want:


Option Explicit

Sub CreateSheets()
Dim SName As String
Dim cell As Range

Application.ScreenUpdating = False

With Worksheets("Main")
For Each cell In .Range("N4", .Cells(.Rows.Count, "N").End(xlUp)).Cells
SName = cell.Value
If SheetExists(SName) = False Then
Sheets("Template").Copy Before:=Sheets("Template")
On Error Resume Next
ActiveSheet.Name = SName
On Error GoTo 0
End If
Next cell
.Select
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub

Function SheetExists(SheetName As String) As Boolean
With ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Len(.Worksheets(SheetName).Name) > 0)
On Error GoTo 0
End With
End Function


It looks at all the cells in N4 through the last used cell in column N.
 
Back
Top