Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto insert copy of worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto insert copy of worksheet
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto insert copy of worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto insert copy of worksheet
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 "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 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 ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-insert same value each Monday in cashflow worksheet? | Excel Worksheet Functions | |||
Insert row in Master worksheet that auto inserts in other workshee | Excel Discussion (Misc queries) | |||
Move/Copy or Copy/Insert worksheet? | Excel Discussion (Misc queries) | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions |