programatically creating worksheets
 

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

You can create sheets right from nothing but it is a horrendous amount of
work. What is your aversion to using a hidden sheet as a template to create
the new sheets. It is easy and the template sheets can be completely hidden
from the end users such that they never even know that they are there...
--
HTH...

Jim Thomlinson

"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

Hi Janet,

If it feasible for you to keep all the forms in one workbook? You can have a
userform open, when the workbook opens, that asks the user what form they
want. They can choose from a dropdown list of the forms you have included in
the workbook. When the choice is made, it makes the chosen form visible and
the other hidden.

Alan


"Janet Panighetti" wrote in
message ...
I would like my workbook to prompt the user for the "type" of a "form" to
add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet?
If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

Don't you need to "unhide" the sheet in order to copy it? If not, then my
question is answered. :)

I just don't want my users to ever see that it is there.... even as a
passing glance.

Thanks

"Jim Thomlinson" wrote:

You can create sheets right from nothing but it is a horrendous amount of
work. What is your aversion to using a hidden sheet as a template to create
the new sheets. It is easy and the template sheets can be completely hidden
from the end users such that they never even know that they are there...
--
HTH...

Jim Thomlinson

"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

Janet

You don't have to have the Template sheet in the workbook at all.

Open a new workbook and delete all but one sheet.

Format that sheet as you wish then Save AsFile TypeMS Excel Template.

Name it SHEET and store it in your XLSTART folder.

To use it when creating new sheets use code similar to this.

Sub Add_Sheets11()
Sheets.Add(Type:="Worksheet").Name = "Newsheet"
End Sub

Or just click on InsertWorksheet which uses your SHEET.xlt as a new sheet.


Gord Dibben MS Excel MVP

On Tue, 20 Feb 2007 15:18:12 -0800, Janet Panighetti
wrote:

Don't you need to "unhide" the sheet in order to copy it? If not, then my
question is answered. :)

I just don't want my users to ever see that it is there.... even as a
passing glance.

Thanks

"Jim Thomlinson" wrote:

You can create sheets right from nothing but it is a horrendous amount of
work. What is your aversion to using a hidden sheet as a template to create
the new sheets. It is easy and the template sheets can be completely hidden
from the end users such that they never even know that they are there...
--
HTH...

Jim Thomlinson

"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

This is the idea, however I still wonder if you have to unhide the form in
order to copy it?

"Alan" wrote:

Hi Janet,

If it feasible for you to keep all the forms in one workbook? You can have a
userform open, when the workbook opens, that asks the user what form they
want. They can choose from a dropdown list of the forms you have included in
the workbook. When the choice is made, it makes the chosen form visible and
the other hidden.

Alan


"Janet Panighetti" wrote in
message ...
I would like my workbook to prompt the user for the "type" of a "form" to
add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet?
If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

Thanks, everyone.

Perhaps even if I do the hidden worksheet thing, the process will move fast
enough that the user won't even notice it too much.

I will also consider using templates. :)


"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

Other users would also have to have the sheet.xlt in their xlstart folder so am
sure my post was a waste of time and bandwidth.

A case of not reading everything that's posted<g


Gord

On Tue, 20 Feb 2007 15:38:55 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Janet

You don't have to have the Template sheet in the workbook at all.

Open a new workbook and delete all but one sheet.

Format that sheet as you wish then Save AsFile TypeMS Excel Template.

Name it SHEET and store it in your XLSTART folder.

To use it when creating new sheets use code similar to this.

Sub Add_Sheets11()
Sheets.Add(Type:="Worksheet").Name = "Newsheet"
End Sub

Or just click on InsertWorksheet which uses your SHEET.xlt as a new sheet.


Gord Dibben MS Excel MVP

On Tue, 20 Feb 2007 15:18:12 -0800, Janet Panighetti
wrote:

Don't you need to "unhide" the sheet in order to copy it? If not, then my
question is answered. :)

I just don't want my users to ever see that it is there.... even as a
passing glance.

Thanks

"Jim Thomlinson" wrote:

You can create sheets right from nothing but it is a horrendous amount of
work. What is your aversion to using a hidden sheet as a template to create
the new sheets. It is easy and the template sheets can be completely hidden
from the end users such that they never even know that they are there...
--
HTH...

Jim Thomlinson

"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

By using very hidden as the visible property and by tobggling screenupdating
the user will see nothing and will not be able to find the template sheet in
the sheets list...

Application.ScreenUpdating = False
Sheet1.Visible = xlSheetVisible
Sheet1.Copy After:=Sheet1
Sheet1.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True

Or something like that...
--
HTH...

Jim Thomlinson


"Janet Panighetti" wrote:

Don't you need to "unhide" the sheet in order to copy it? If not, then my
question is answered. :)

I just don't want my users to ever see that it is there.... even as a
passing glance.

Thanks

"Jim Thomlinson" wrote:

You can create sheets right from nothing but it is a horrendous amount of
work. What is your aversion to using a hidden sheet as a template to create
the new sheets. It is easy and the template sheets can be completely hidden
from the end users such that they never even know that they are there...
--
HTH...

Jim Thomlinson

"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet? If
so, I would like to store that information in a function to run on demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet

programatically creating worksheets
 

Sub Add_A_Sheet()
Worksheets.Add
Set sh = ActiveSheet
Worksheets("HiddenSheet").Cells.Copy sh.Cells
End Sub

worked fine for me even when the hiddensheet was xlVeryHidden (and it is
never unhidden).



--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"Janet Panighetti" wrote in
message ...
Thanks, everyone.

Perhaps even if I do the hidden worksheet thing, the process will move
fast
enough that the user won't even notice it too much.

I will also consider using templates. :)


"Janet Panighetti" wrote:

I would like my workbook to prompt the user for the "type" of a "form" to
add
to the workbook, and then run a funtion which creates that type of a
worksheet.

In thinking about this, I have a question......

Is it possible to retrieve the structure of a worksheet (cell formats,
values, borders, patterns, protection, etc.) from an existing worksheet?
If
so, I would like to store that information in a function to run on
demand.

I do not want to have these forms hidden in the workbook.

Did I make this question clear or would you like me to be more specific?

Thank you in advance!! :)

Janet