Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets and auto listing them
Hi there
I'm looking for help to automatically create a new worksheet (based o a defined template preferably)...the new worksheet would be create based on the name of a new item in a list of items - as soon as a ne item is added... Alternatively I could create all the worksheets (hide the ones/templat worksheets that are not in use), but then I need a formula to list al the worksheet names in a list/coloumn...and update the list as soon a a worksheet name is changed. Any ideas? Thanks in advance Ja -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a listing from a drop down box | Excel Discussion (Misc queries) | |||
Listing the names of your worksheets | Excel Discussion (Misc queries) | |||
Find duplicate entries within mulitple worksheets, then listing th | Excel Worksheet Functions | |||
Listing data from multiple worksheets | Excel Worksheet Functions | |||
Can Sales Listing from Worksheet #1 go to individual worksheets? | Excel Worksheet Functions |