auto insert copy of worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Dim wks As Workshee
Dim myVal As Strin
Dim resp As Lon

'too many cells at once
If Target.Cells.Count > 1 Then Exit Su

'Must be in column A (=1
If Target.Column <> 1 Then Exit Su

'must be after row
If Target.Row < 2 Then Exit Su

myVal = CStr(Target.Value

Set wks = Nothin
On Error Resume Nex
Set wks = Worksheets(myVal
On Error GoTo

If wks Is Nothing The
'worksheet doesn't already exis
Set wks = Worksheets.Add(after:=Target.Parent
Me.Activat
On Error Resume Nex
wks.Name = myVa
If Err.Number > 0 The
Application.ScreenUpdating = Tru
If MsgBox(prompt:="Can't add this sheet." & vbLf &
"Should I delete the new one?",
Buttons:=vbYesNo + vbCritical,
Title:="Warning") = vbYes The
Application.DisplayAlerts = Fals
wks.Delet
Application.DisplayAlerts = Tru
Els
MsgBox "Please Rename " & wks.Name & " manually
End I
Application.ScreenUpdating = Fals
End I
On Error GoTo
Els
MsgBox "A worksheet named " & wks.Name & " already exists" &
vbLf & "Not added!", Buttons:=vbCritica
End I

End Su
 
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 said:
I want to create a workbook to track test anomalies. The first worksheet
Click to expand...
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.
 
Back
Top