Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 23rd 04, 03:59 PM
Mark Mulik
 
Posts: n/a
Default 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

  #2   Report Post  
Old November 23rd 04, 05:55 PM
crispbd
 
Posts: n/a
Default


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

  #3   Report Post  
Old November 28th 04, 02:21 AM
Steve Smallman
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer data from sheet to sheet Jenn Excel Discussion (Misc queries) 4 January 20th 05 03:07 PM
Negative numbers turn positive automatically on data entry Jerri Excel Discussion (Misc queries) 4 January 8th 05 05:05 PM
Amount or Numbers in Words ron New Users to Excel 6 December 24th 04 07:32 PM
Automatically insert fill colour when data is added? suzi Excel Worksheet Functions 2 November 17th 04 02:55 PM
Automatically pull data into another worksheet within the same fil TJess Excel Worksheet Functions 1 November 15th 04 08:00 PM


All times are GMT +1. The time now is 02:28 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017