I'd like to create a new tab for each cell in column A that has a value in it
using that cell's value as the tab name. I have a tab that the new ones
should all be copies of named "Template Tab". I'd like this to loop until it
finds a blank cell in column A and then stop.

Thanks in advance for your help!!

maybe something like this:

Sub test()
Dim lRow As Long
Dim sht As Worksheet
Dim shtTest As Worksheet

lRow = 1
Set sht = ActiveSheet
While sht.Range("A" & lRow).Text < ""
With sht.Range("A" & lRow)
On Error Resume Next
Set shtTest = Nothing
Set shtTest = Worksheets(.Text)
On Error GoTo 0
If shtTest Is Nothing Then
Worksheets("Template Tab").Copy After:=sht
ActiveSheet.Name = .Text
Else
Debug.Print .Text; " tab already exists"
End If
lRow = lRow + 1
End With
Wend
sht.Activate

End Sub

--
Hope that helps.

Vergel Adriano


"Mr. Matt" wrote:

I'd like to create a new tab for each cell in column A that has a value in it
using that cell's value as the tab name. I have a tab that the new ones
should all be copies of named "Template Tab". I'd like this to loop until it
finds a blank cell in column A and then stop.

Thanks in advance for your help!!

Not saying that it will happen but copying worksheets has an inherent bug in
it. If you start running into an error like:
Run-time error '1004':
Copy Method of Worksheet Class failed
then check out this link for a work around...

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"Vergel Adriano" wrote:

maybe something like this:

Sub test()
Dim lRow As Long
Dim sht As Worksheet
Dim shtTest As Worksheet

lRow = 1
Set sht = ActiveSheet
While sht.Range("A" & lRow).Text < ""
With sht.Range("A" & lRow)
On Error Resume Next
Set shtTest = Nothing
Set shtTest = Worksheets(.Text)
On Error GoTo 0
If shtTest Is Nothing Then
Worksheets("Template Tab").Copy After:=sht
ActiveSheet.Name = .Text
Else
Debug.Print .Text; " tab already exists"
End If
lRow = lRow + 1
End With
Wend
sht.Activate

End Sub

--
Hope that helps.

Vergel Adriano


"Mr. Matt" wrote:

I'd like to create a new tab for each cell in column A that has a value in it
using that cell's value as the tab name. I have a tab that the new ones
should all be copies of named "Template Tab". I'd like this to loop until it
finds a blank cell in column A and then stop.

Thanks in advance for your help!!

This is great, thanks a lot. The only thing I'd like to change is that the
last tab created be the last tab in the workbook. In other words, it would
be great if the first new tab were created at the end of all other existing
tabs and the rest of the new tabs followed that one with the final one being
last in the workbook.

Thanks again.

"Vergel Adriano" wrote:

maybe something like this:

Sub test()
Dim lRow As Long
Dim sht As Worksheet
Dim shtTest As Worksheet

lRow = 1
Set sht = ActiveSheet
While sht.Range("A" & lRow).Text < ""
With sht.Range("A" & lRow)
On Error Resume Next
Set shtTest = Nothing
Set shtTest = Worksheets(.Text)
On Error GoTo 0
If shtTest Is Nothing Then
Worksheets("Template Tab").Copy After:=sht
ActiveSheet.Name = .Text
Else
Debug.Print .Text; " tab already exists"
End If
lRow = lRow + 1
End With
Wend
sht.Activate

End Sub

--
Hope that helps.

Vergel Adriano


"Mr. Matt" wrote:

I'd like to create a new tab for each cell in column A that has a value in it
using that cell's value as the tab name. I have a tab that the new ones
should all be copies of named "Template Tab". I'd like this to loop until it
finds a blank cell in column A and then stop.

Thanks in advance for your help!!

this should do it then..

Sub test()
Dim lRow As Long
Dim sht As Worksheet
Dim shtTest As Worksheet

lRow = 1
Set sht = ActiveSheet
While sht.Range("A" & lRow).Text < ""
With sht.Range("A" & lRow)
On Error Resume Next
Set shtTest = Nothing
Set shtTest = Worksheets(.Text)
On Error GoTo 0
If shtTest Is Nothing Then
Worksheets("Template Tab").Copy
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = .Text
Else
Debug.Print .Text; " tab already exists"
End If
lRow = lRow + 1
End With
Wend
sht.Activate

End Sub





--
Hope that helps.

Vergel Adriano


"Mr. Matt" wrote:

This is great, thanks a lot. The only thing I'd like to change is that the
last tab created be the last tab in the workbook. In other words, it would
be great if the first new tab were created at the end of all other existing
tabs and the rest of the new tabs followed that one with the final one being
last in the workbook.

Thanks again.

"Vergel Adriano" wrote:

maybe something like this:

Sub test()
Dim lRow As Long
Dim sht As Worksheet
Dim shtTest As Worksheet

lRow = 1
Set sht = ActiveSheet
While sht.Range("A" & lRow).Text < ""
With sht.Range("A" & lRow)
On Error Resume Next
Set shtTest = Nothing
Set shtTest = Worksheets(.Text)
On Error GoTo 0
If shtTest Is Nothing Then
Worksheets("Template Tab").Copy After:=sht
ActiveSheet.Name = .Text
Else
Debug.Print .Text; " tab already exists"
End If
lRow = lRow + 1
End With
Wend
sht.Activate

End Sub

--
Hope that helps.

Vergel Adriano


"Mr. Matt" wrote:

I'd like to create a new tab for each cell in column A that has a value in it
using that cell's value as the tab name. I have a tab that the new ones
should all be copies of named "Template Tab". I'd like this to loop until it
finds a blank cell in column A and then stop.

Thanks in advance for your help!!