Hi Jac,
Create a template sheet called Template and hide it.
Put this code in the worksheet code module of the worksheet that maintains
the list (right-click on the sheet tab, select View Code, and paste the
code).
It assumes the list is in A1:A10, adjust to suit.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oWs As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
On Error Resume Next
Set oWs = ActiveWorkbook.Worksheets(Target.Value)
On Error GoTo ws_exit
If oWs Is Nothing Then
Worksheets("Template").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"jacurrie " wrote in message
...
Hi there
I'm looking for help to automatically create a new worksheet (based on
a defined template preferably)...the new worksheet would be created
based on the name of a new item in a list of items - as soon as a new
item is added...
Alternatively I could create all the worksheets (hide the ones/template
worksheets that are not in use), but then I need a formula to list all
the worksheet names in a list/coloumn...and update the list as soon as
a worksheet name is changed.
Any ideas?
Thanks in advance
Jac
---
Message posted from http://www.ExcelForum.com/