Making a copy of worksheet

[M Hill]

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

 

[Nikos Yannacopoulos]

The following macro does it... I would suggest you save it
in your personal.xls rather than Notes.xlt, or it will be
saved in every new copy and increase the file size for no
reason. Change the path, and have a look at the comments
describing what the last few lines do; to skip a line
comment it out with an ' (apostrophe) at the beginning.

Sub Macro1()
pth = "c:\documents\spreadsheets\" 'modify with your
path here
fnam = ActiveCell.Row
newfilename = pth & fnam & ".xls"
Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=newfilename, _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save 'saves new workbook
Workbooks.Open "C:\Documents and
Settings\G3488NY\Application
Data\Microsoft\Templates\Notes.xlt" 'reopens template /
modify path
Windows(fnam & ".xls").Close 'closes new
workbook
End Sub

HTH,
Nikos