Function to automatically insert a new sheet as a result of data entry?
 

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

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

Mark,

when faced with this in the past, I have used a template sheet in the same
workbook, much simpler.

To ensure the data is entered the way I want it, and the macro triggered
using all appropriate parameters, I protect the master sheet, forcing the
users to add the new entry via a user defined form, checking that the data
is appropriate prior to generating new sheet.

link your form to a button at the top of the master sheet, and on the open
event write the next sequential project number from your list, open the form
modal so that the user cannot leave the form without clicking on the create
project or cancel buttons. Then pass control to the user, If cancel clicked
do nothing, If create, then verify all data written to the form, dates are
dates and end date of p[roject not before start date, names are valid names
etc, use these parameters to create your sheet, your entry in master and
hyperlink the master to the project sheet.

A little bit of work, but the results in quality control of data entry are
worth it.

steve
"Mark Mulik" wrote in message
m...
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