Macro REQ: Automatically copy a template sheet and rename

[MCSmarties]

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/micr...a644cc13c7/01006249e7bf4ed4?#01006249e7bf4ed4

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!

 

[Trevor Shuttleworth]

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]

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 are:
- 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

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

<snip>

 

[Trevor Shuttleworth]

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]

Awesome macro Trevor, many thanks!

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>

 

[Trevor Shuttleworth]

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