Sorry again. Last time I will post without testing JE
--
---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com
"M Hill" wrote in message
...
Hi JE,
Thank you for the code, I just dont know how to call your code from TOC.
--
---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com
"J.E. McGimpsey" wrote in message
...
One way:
Put this in the worksheet code module of sheet TOC:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim wkSht As Worksheet
Dim result As Integer
Dim sName As String
With Target
If .Count 1 Then Exit Sub
sName = .Row
End With
On Error Resume Next
Set wkSht = Worksheets(sName)
On Error GoTo 0
If Not wkSht Is Nothing Then
result = MsgBox( _
Prompt:="Delete current sheet " & sName & "?", _
Buttons:=vbYesNo)
If result = vbNo Then
With Application
.EnableEvents = False
.Undo
.Goto Target
.EnableEvents = True
Exit Sub
End With
Else
Application.DisplayAlerts = False
wkSht.Delete
Application.DisplayAlerts = True
End If
End If
Worksheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = sName
End Sub
In article ,
"Simon" wrote:
Hi guys, any help here greatly appreciated.
I have an excel template file called Notes.xlt
It has 2 worksheets in it called 'TOC' and 'TEMPLATE'
In 'TOC', I will only be using column A, starting at Cell A1 and
working
down column A.
What I would like to achieve is the following:
Worksheet 'TOC'
A
1 This is cell A1
2 cell A2
3 cell A3
Through some sort of macro, I would like to be able to enter in text
into a
cell in column A, then according to the row number eg.'2',
copy the TEMPLATE worksheet to a new worksheet, and rename it to that
row
number, so the name of the new worksheet becomes '2'.
If I accidentally try and create the new worksheet that exists with
this
name, to prompt me to give me the option of recreating it, or cancel.
Is this possible in excel?
Any help is really appreciated.