Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create & Name new Sheets without duplicates
Need to create new worksheets from a template ws, then name them from a
list on a sheet called "CatNames". The sub below from Dave P. works fine, except i would like to avoid creating duplicate sheets. If my list in Col A contains 12 names, i insert the new worksheets. that works fine. if i add 8 names to the list tomorrow, i need to create ONLY the 8 new sheets. make sense? any help offered will be gladly received. Many TIA Sub CreateNameSheets() ' by Dave Peterson Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("qwerty") Set ListWks = Worksheets("CatNames") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy after:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Duplicate Worksheet " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create & Name new Sheets without duplicates
Check to see if a worksheet with that name exists before adding it:
Option Explicit Sub CreateNameSheets() ' by Dave Peterson Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range dim Wks as worksheet dim resp as long Set TemplateWks = Worksheets("qwerty") Set ListWks = Worksheets("CatNames") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells set wks = nothing on error resume next set wks = worksheets(mycell.value) on error goto 0 if wks is nothing then 'doesn't exist, so add it TemplateWks.Copy _ after:=Worksheets(Worksheets.Count) on error resume next ActiveSheet.Name = myCell.Value if err.number < 0 then 'delete it? application.displayalerts = false activesheet.delete application.displayalerts = true err.clear end if else beep 'or msgbox mycell.value & " already exists" end if Next myCell End Sub I deleted the newly added sheet if the name was invalid. wrote: Need to create new worksheets from a template ws, then name them from a list on a sheet called "CatNames". The sub below from Dave P. works fine, except i would like to avoid creating duplicate sheets. If my list in Col A contains 12 names, i insert the new worksheets. that works fine. if i add 8 names to the list tomorrow, i need to create ONLY the 8 new sheets. make sense? any help offered will be gladly received. Many TIA Sub CreateNameSheets() ' by Dave Peterson Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("qwerty") Set ListWks = Worksheets("CatNames") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy after:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Duplicate Worksheet " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create & Name new Sheets without duplicates
Dave - MANY Thanks! works like unto a treat!!!
Happy NW Dave Peterson wrote: Check to see if a worksheet with that name exists before adding it: Option Explicit Sub CreateNameSheets() ' by Dave Peterson Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range dim Wks as worksheet dim resp as long Set TemplateWks = Worksheets("qwerty") Set ListWks = Worksheets("CatNames") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells set wks = nothing on error resume next set wks = worksheets(mycell.value) on error goto 0 if wks is nothing then 'doesn't exist, so add it TemplateWks.Copy _ after:=Worksheets(Worksheets.Count) on error resume next ActiveSheet.Name = myCell.Value if err.number < 0 then 'delete it? application.displayalerts = false activesheet.delete application.displayalerts = true err.clear end if else beep 'or msgbox mycell.value & " already exists" end if Next myCell End Sub I deleted the newly added sheet if the name was invalid. wrote: Need to create new worksheets from a template ws, then name them from a list on a sheet called "CatNames". The sub below from Dave P. works fine, except i would like to avoid creating duplicate sheets. If my list in Col A contains 12 names, i insert the new worksheets. that works fine. if i add 8 names to the list tomorrow, i need to create ONLY the 8 new sheets. make sense? any help offered will be gladly received. Many TIA Sub CreateNameSheets() ' by Dave Peterson Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("qwerty") Set ListWks = Worksheets("CatNames") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy after:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Duplicate Worksheet " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlighting duplicates across many sheets | Excel Worksheet Functions | |||
Need Help with Highlighting duplicates across several sheets | Excel Worksheet Functions | |||
counting duplicates Among Many Sheets, Possible?? | New Users to Excel | |||
Deleting duplicates across sheets p. 2 | Excel Programming | |||
Deleting duplicates across sheets | Excel Programming |