Creating a copy of a worksheet

[Simon]

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.


--


---------------------------------------------------------------------
"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]

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

 

[M Hill]

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

 

[M Hill]

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

 

[Simon]

JE, thank you.

It does what I want thank you.
Just 2 more things Id like with this project...

To recap:
2 worksheets: 'TOC' and 'TEMPLATE'

So far your code:
Creates a new worksheet when I enter in text on TOC based on the template
worksheet and assigns it the new name of the row number I am in from the TOC
worksheet (fantastic!).


When I enter in text into TOC worksheet, lets say in cell A3 (or any row
from TOC):
I would like the text I type into this cell to be entered into cell A1
(always cell A1 no matter what row I am in from TOC) of the newly created
worksheet.
I would then like cell I am currently in (eg.A3) of the TOC worksheet to be
hyperlinked to the newly created worksheet.


Your great code....

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


--


---------------------------------------------------------------------
"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