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