Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,089
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,089
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,089
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to: Add new sheet, then rename new sheet with todays date Paul Excel Worksheet Functions 3 September 29th 07 03:17 AM
How do I automatically rename a sheet from the summary page? Aaron Excel Worksheet Functions 1 September 13th 06 04:50 PM
automatically making a copy of a sheet starguy Excel Worksheet Functions 2 April 22nd 06 06:37 AM
Copy/Rename a sheet DK Links and Linking in Excel 1 March 20th 06 06:36 AM
How do I automatically rename a sheet with the contents of a cell. michaelspearin Excel Discussion (Misc queries) 3 December 3rd 04 10:27 PM


All times are GMT +1. The time now is 12:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"