If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|