Create Tabs from Existing Tab
 

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
-----------------------------------------------------------------------------------

Create Tabs from Existing Tab
 

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
-----------------------------------------------------------------------------------

Create Tabs from Existing Tab
 

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
-----------------------------------------------------------------------------------