ExcelBanter - Creating worksheets and auto listing them

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/