This macro will place a copy of sheet(1) from a specified template
into the current workbook at the end (with name project_##,
incrementing numbers based on sheet # )

Sub InsertNewSheetFromTemplate

Application.ScreenUpdating = False
Dim NB As Object, TB As Object
Set TB = ThisWorkbook
Set NB = Workbooks.Add("TemplateDirectory\YourTemplateName. xlt")
NB.Sheets(1).Copy After:=TB.Sheets(TB.Sheets.Count)
NB.Close
TB.Activate
TB.Sheets(TB.Sheets.Count).Name = "Project_" & TB.Sheets.Count
Application.ScreenUpdating = True

If you want it to automatically do this based on data entry,
modify the data entry page's 'Worksheet_Change' event to trigger the
above macro.







Mark Mulik Wrote:
Howdy.

I'm trying to figure out the best way that I can have Excel
automatically insert a new worksheet when I enter data on another
sheet -- be it a worksheet function, macro or something else. This is
really two questions:

1. I have a "master" sheet in the document called ProjectList, and
then a bunch of sheets called Project_# with incrementing numbers for
projects as they are created. I'd like to be able to make it so that
as I enter text for new projects in the ProjectList sheet, a new
Project_# sheet (with the appropriate # as I entered it on the
ProjectList sheet) be created from a template. Excel probably isn't
the best tool for this, but I'm trying to work within constraints that
say Excel is the tool of choice.

2. I've figured out how to use macros to insert blank sheets, but I
haven't figured out how to get it to insert a sheet from a template
file. If I can't do it from a template, I can have a sheet in the
workbook that I leave unmolested, such that I can have the
macro/function make a copy of it -- if that's doable and working from
a template isn't.

The ideal solution would have it so I can have a user enter data in
the master sheet, and new sheets based on a template be created
automatically. If that's not workable, then I'd settle for a macro
button that would insert sheets using a template as the basis. If the
latter is the most workable solution, then the user should be asked
for the number of the project, such that the sheet would be named with
Project_ and that number.

Thanks for any suggestions you may offer about this. I have a fair
solution doing it manually right now, but I'd like to make it even
simpler for others to use.

Thanks.
Mark


--
crispbd
------------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...o&userid=10880
View this thread: http://www.excelforum.com/showthread...hreadid=319751