View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CJ CJ is offline
external usenet poster
 
Posts: 18
Default Create new worksheet from template for each value in range

Thanks... it didn't work for what I needed..

I ended up with this that works:
However it doesn't do the step 3 I wanted... can you help with that?

Sub CreateSheets()
Dim rng As Range, rngNames As Range
Dim szSheetName As String
Dim wks As Worksheet

'Turn off the screen
Application.ScreenUpdating = False

'Get the list of names
With ThisWorkbook.Worksheets("TOC")
Set rngNames = .Range(.Range("A6"), .Range("A6").End(xlDown))
End With

'Loop through the list of names
For Each rng In rngNames
'Store the name for the worksheet
szSheetName = Left$(rng.Text, 31)

'See if the sheet already exists
On Error Resume Next 'Suppress an error if sheet not found
Set wks = Nothing
Set wks = ThisWorkbook.Worksheets(szSheetName)
On Error GoTo 0

'If it doesn't exist, create it
If wks Is Nothing Then
ThisWorkbook.Worksheets("template").Copy
Befo=ThisWorkbook.Worksheets("template")
ActiveSheet.Name = szSheetName
End If
Next rng

End Sub