auto insert copy of worksheet

[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 TA003, 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 (cell A1 says "Date", cell A2 says "Title", etc) Is there any way that it can automatically insert a copy of the 2nd worksheet? 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

 

[Kieran]

Something like;

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)
wks.Cells(1, 1).Value = "Date"
wks.Cells(1, 2).Value = "Title"
wks.Cells(1, 3).Value = "Anything else you want to add"
'etc
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 Su

 

[Guest]

Hi Kieran
Thanks for the quick reply. Is there a way to auto insert a copy of a particular worksheet? (e.g., insert a copy of worksheet named "template") Thanks in advance

Carlos

 

[Nigel]

Presumably you only want parts of the worksheets(2) to appear on the new
worksheet? Best approach would be directly assign the value(s) from sheet 2
to a cell or range of cells on the new blank worksheet after the me.activate
statement. Something along the lines of the following should do it.

Range("A1:A6").Value = Sheets(2).Range("A1:K6").Value
Range("Z9").Value = Sheets(2).Range("Z9").Value

Cheers
Nigel

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 TA003, 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 (cell A1 says
"Date", cell A2 says "Title", etc) Is there any way that it can
automatically insert a copy of the 2nd worksheet? Any help will be greatly
appreciated. Thanks in advance.

 

[Nigel]

Hi,
Here is a modified version that will copy a worksheet named template (which
you might want to hide).
and then name it to the entry made on the sheet with this code
Cheers
Nigel


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

Sheets("Template").copy Before:=Sheets("Template")
Worksheets("Template (2)").Name = myVal
Worksheets(myVal).Visible = True

Me.Activate

On Error Resume Next
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

Nigel,
Thanks for the info. But, actually I do need to copy the entire worksheet

because I also want to copy the formatting, row height, headers and footers,
etc. Is there a way to modify the code below so that when I enter a number
on the first log sheet (say "TA001") a copy of the worksheet named
"template" is created with the worksheet name "TA001"? Thanks in advance.