auto insert copy of worksheet

Carlos,

If you insert a worksheet that you want to use as a template, formatted in
the way you like, then you can do something like:

Dim mySht As Worksheet
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
Set mySht = Worksheets(Worksheets.Count)
mySht.Name = "TA" & Format(Worksheets.Count - 1, "000")

HTH,
Bernie
MS Excel MVP

"Carlos Munoz" wrote in message
...
I want to create a workbook to track test anomalies. The first worksheet
is a log sheet and then each subsequent worksheet is the detailed
information about that anomaly. (i.e., the 2nd worksheet would be named
TA001, the 3rd worksheet would be named TA002, etc.) I found the following
macro that auto inserts worksheets named whatever you enter on the first
worksheet (in this case the log). So, as new test anomalies are entered on
the log sheet, a new worksheet for that test anomaly is inserted. However,
I want the worksheet that are inserted to all be the same format. Is there
any way that it can automatically insert a copy of the 2nd worksheet or a
template worksheet I define? Any help will be greatly appreciated. Thanks
in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim myVal As String
Dim resp As Long

'too many cells at once!
If Target.Cells.Count 1 Then Exit Sub

'Must be in column A (=1)
If Target.Column 1 Then Exit Sub

'must be after row 1
If Target.Row 2 Then Exit Sub

myVal = CStr(Target.Value)

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myVal)
On Error GoTo 0

If wks Is Nothing Then
'worksheet doesn't already exist
Set wks = Worksheets.Add(after:=Target.Parent)
Me.Activate
On Error Resume Next
wks.Name = myVal
If Err.Number 0 Then
Application.ScreenUpdating = True
If MsgBox(prompt:="Can't add this sheet." & vbLf & _
"Should I delete the new one?", _
Buttons:=vbYesNo + vbCritical, _
Title:="Warning") = vbYes Then
Application.DisplayAlerts = False
wks.Delete
Application.DisplayAlerts = True
Else
MsgBox "Please Rename " & wks.Name & " manually"
End If
Application.ScreenUpdating = False
End If
On Error GoTo 0
Else
MsgBox "A worksheet named " & wks.Name & " already exists" & _
vbLf & "Not added!", Buttons:=vbCritical
End If

End Sub