Create Tabs from Existing Tab

The Last Topic was getting too long for History See
http://groups.google.com/group/micro...786c2446bdc465

With much help from Bob, I have a VB code in which I am creating new
tabs from a list and existing tab called template. The code works fine
with one exception that at the end it wants to create another tab with
the same name Template and run to an run time error for duplication of
existing tabs and creates a one called Template(2).

-------------------------------------Existing Code
Private Sub CommandButton1_Click()
' Create tabs from the list
' Bob from microsoft.public.excel.programming 11/28/2006

Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet

Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A2", LastCell)

For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub
-----------------------------------------------------------------------------------

One more thing.... With testing the code seem to work with 25 name on
list and run to the above error with the any more names i.e. 26 and up.
Ardy wrote:
> Create Tabs from Existing Tab
>
> The Last Topic was getting too long for History See
> http://groups.google.com/group/micro...786c2446bdc465
>
> With much help from Bob, I have a VB code in which I am creating new
> tabs from a list and existing tab called template. The code works fine
> with one exception that at the end it wants to create another tab with
> the same name Template and run to an run time error for duplication of
> existing tabs and creates a one called Template(2).
>
> -------------------------------------Existing Code
> Private Sub CommandButton1_Click()
> ' Create tabs from the list
> ' Bob from microsoft.public.excel.programming 11/28/2006
>
> Dim LastCell As Range, Rng As Range, cell As Range
> Dim WS As Worksheet
>
> Set WS = ActiveSheet
> Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
> Set Rng = WS.Range("A2", LastCell)
>
> For Each cell In Rng
> If Not IsEmpty(cell) Then
> Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
> ActiveSheet.Name = cell.Value
> End If
> Next
> End Sub
> -----------------------------------------------------------------------------------

You are right........There was one of the name that was repeating.
Thanks
John Bundy wrote:
> The reason it is stopping is because in your list, one of the words you are
> using to name a sheet with in the name of an already existing sheet. If i'm
> not mistaken the max length of a sheet name is 31 characters including spaces
> and symbols.
>
> -John
>
> "Ardy" wrote:
>
> > One more thing.... With testing the code seem to work with 25 name on
> > list and run to the above error with the any more names i.e. 26 and up.
> > Ardy wrote:
> > > Create Tabs from Existing Tab
> > >
> > > The Last Topic was getting too long for History See
> > > http://groups.google.com/group/micro...786c2446bdc465
> > >
> > > With much help from Bob, I have a VB code in which I am creating new
> > > tabs from a list and existing tab called template. The code works fine
> > > with one exception that at the end it wants to create another tab with
> > > the same name Template and run to an run time error for duplication of
> > > existing tabs and creates a one called Template(2).
> > >
> > > -------------------------------------Existing Code
> > > Private Sub CommandButton1_Click()
> > > ' Create tabs from the list
> > > ' Bob from microsoft.public.excel.programming 11/28/2006
> > >
> > > Dim LastCell As Range, Rng As Range, cell As Range
> > > Dim WS As Worksheet
> > >
> > > Set WS = ActiveSheet
> > > Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
> > > Set Rng = WS.Range("A2", LastCell)
> > >
> > > For Each cell In Rng
> > > If Not IsEmpty(cell) Then
> > > Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
> > > ActiveSheet.Name = cell.Value
> > > End If
> > > Next
> > > End Sub
> > > -----------------------------------------------------------------------------------
> >
> >