Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro REQ: Automatically copy a template sheet and rename
Hello,
I would like Excel to create a new sheet when a value is pasted to an (empty) cell in a template sheet. Details: - the template sheet (which already contains formulae) should treat anything pasted into a particular range as a VALUE. - the new sheet should be auto-renamed to the value of a cell in the template sheet - the "template" should revert to the state BEFORE data was pasted into it - the focus remains on the "new" sheet and any additional data pasted into it is treated as VALUES. The discussion below ("Auto Rename Excel Sheets in Workbook") already comes very close to what I need. http://groups.google.com/group/micro...006249e7bf4ed4 From the last contribution, I tried the macro: Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub Set DestWs = Sheets("template").copy(after:=Sheets(Sheets.Count )) On Error Resume Next DestWs.Name = Range("F3").Value On Error GoTo 0 End Sub But: 1. I get an error message "Run-time error '424': Object required" 2. The template sheet contains the pasted data at the end 3. There's no "paste as values"handling Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro REQ: Automatically copy a template sheet and rename
Try:
Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet Dim DestName As String If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub DestName = Target.Value ' store sheet name Application.EnableEvents = False Target.Value = "" ' clear template Application.EnableEvents = True Sheets("template").Copy after:=Sheets(Sheets.Count) Set DestWs = ActiveSheet On Error Resume Next DestWs.Name = DestName ' use stored name instead of cell value On Error GoTo 0 End Sub Regards Trevor "MCSmarties" wrote in message s.com... Hello, I would like Excel to create a new sheet when a value is pasted to an (empty) cell in a template sheet. Details: - the template sheet (which already contains formulae) should treat anything pasted into a particular range as a VALUE. - the new sheet should be auto-renamed to the value of a cell in the template sheet - the "template" should revert to the state BEFORE data was pasted into it - the focus remains on the "new" sheet and any additional data pasted into it is treated as VALUES. The discussion below ("Auto Rename Excel Sheets in Workbook") already comes very close to what I need. http://groups.google.com/group/micro...006249e7bf4ed4 From the last contribution, I tried the macro: Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub Set DestWs = Sheets("template").copy(after:=Sheets(Sheets.Count )) On Error Resume Next DestWs.Name = Range("F3").Value On Error GoTo 0 End Sub But: 1. I get an error message "Run-time error '424': Object required" 2. The template sheet contains the pasted data at the end 3. There's no "paste as values"handling Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro REQ: Automatically copy a template sheet and rename
Thanks a lot for the answer Trevor.
Basically your macro works, but not in the way I need it to. I should have specified is that I am pasting a range of values covering more than just the single F3 cell. I want to paste VALUES (calculated via formulas in another workbook) covering the range F3:F24 at once. The new sheet should be renamed to what was pasted into F3 and RETAIN all values in F3:F24, while the original "Template" sheet reverts to its original state and is ready to be used again. With your macro, the problems a - If I try to paste values into F3:F24, I get the error "Run-time error '13': Type mismatch." - The new sheet and the template sheet are identical (eg the value pasted in F3 disappears). Thanks for your help and sorry that I was unclear in my first post! Martin On Oct 9, 4:46 pm, "Trevor Shuttleworth" wrote: Try: Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet Dim DestName As String If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub DestName = Target.Value ' store sheet name Application.EnableEvents = False Target.Value = "" ' clear template Application.EnableEvents = True Sheets("template").Copy after:=Sheets(Sheets.Count) Set DestWs = ActiveSheet On Error Resume Next DestWs.Name = DestName ' use stored name instead of cell value On Error GoTo 0 End Sub Regards Trevor "MCSmarties" wrote in message s.com... Hello, I would like Excel to create a new sheet when a value is pasted to an (empty) cell in a template sheet. Details: - the template sheet (which already contains formulae) should treat anything pasted into a particular range as a VALUE. - the new sheet should be auto-renamed to the value of a cell in the template sheet - the "template" should revert to the state BEFORE data was pasted into it - the focus remains on the "new" sheet and any additional data pasted into it is treated as VALUES. <snip |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro REQ: Automatically copy a template sheet and rename
OK, maybe this will do what you need:
Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub Sheets("template").Copy after:=Sheets(Sheets.Count) Set DestWs = ActiveSheet On Error Resume Next DestWs.Name = Range("F3").Value On Error GoTo 0 Application.EnableEvents = False Target.Value = "" ' clear template Application.EnableEvents = True End Sub Regards Trevor "MCSmarties" wrote in message ups.com... Thanks a lot for the answer Trevor. Basically your macro works, but not in the way I need it to. I should have specified is that I am pasting a range of values covering more than just the single F3 cell. I want to paste VALUES (calculated via formulas in another workbook) covering the range F3:F24 at once. The new sheet should be renamed to what was pasted into F3 and RETAIN all values in F3:F24, while the original "Template" sheet reverts to its original state and is ready to be used again. With your macro, the problems a - If I try to paste values into F3:F24, I get the error "Run-time error '13': Type mismatch." - The new sheet and the template sheet are identical (eg the value pasted in F3 disappears). Thanks for your help and sorry that I was unclear in my first post! Martin On Oct 9, 4:46 pm, "Trevor Shuttleworth" wrote: Try: Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet Dim DestName As String If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub DestName = Target.Value ' store sheet name Application.EnableEvents = False Target.Value = "" ' clear template Application.EnableEvents = True Sheets("template").Copy after:=Sheets(Sheets.Count) Set DestWs = ActiveSheet On Error Resume Next DestWs.Name = DestName ' use stored name instead of cell value On Error GoTo 0 End Sub Regards Trevor "MCSmarties" wrote in message s.com... Hello, I would like Excel to create a new sheet when a value is pasted to an (empty) cell in a template sheet. Details: - the template sheet (which already contains formulae) should treat anything pasted into a particular range as a VALUE. - the new sheet should be auto-renamed to the value of a cell in the template sheet - the "template" should revert to the state BEFORE data was pasted into it - the focus remains on the "new" sheet and any additional data pasted into it is treated as VALUES. <snip |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro REQ: Automatically copy a template sheet and rename
Awesome macro Trevor, many thanks!
On Oct 10, 4:34 pm, "Trevor Shuttleworth" wrote: OK, maybe this will do what you need: Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub Sheets("template").Copy after:=Sheets(Sheets.Count) Set DestWs = ActiveSheet On Error Resume Next DestWs.Name = Range("F3").Value On Error GoTo 0 Application.EnableEvents = False Target.Value = "" ' clear template Application.EnableEvents = True End Sub Regards Trevor <snip |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro REQ: Automatically copy a template sheet and rename
I'll take that as a "yes". You're welcome. Thanks for the feedback.
Anyway, I think if you look closely, most of the code is from your original. Regards Trevor "MCSmarties" wrote in message oups.com... Awesome macro Trevor, many thanks! On Oct 10, 4:34 pm, "Trevor Shuttleworth" wrote: OK, maybe this will do what you need: Private Sub Worksheet_Change(ByVal Target As Range) Dim DestWs As Worksheet If Me.Name < "Template" Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub Sheets("template").Copy after:=Sheets(Sheets.Count) Set DestWs = ActiveSheet On Error Resume Next DestWs.Name = Range("F3").Value On Error GoTo 0 Application.EnableEvents = False Target.Value = "" ' clear template Application.EnableEvents = True End Sub Regards Trevor <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to: Add new sheet, then rename new sheet with todays date | Excel Worksheet Functions | |||
How do I automatically rename a sheet from the summary page? | Excel Worksheet Functions | |||
automatically making a copy of a sheet | Excel Worksheet Functions | |||
Copy/Rename a sheet | Links and Linking in Excel | |||
How do I automatically rename a sheet with the contents of a cell. | Excel Discussion (Misc queries) |