'Template' within a worksheet - change existing worksheets
 

Hello,

I have a workbook that tracks production data for an entire month. In
addition to charts and tables, it has 62 nearly identical worksheets that
serve as forms for the user to enter data for each shift (2 shifts per 31
days).

I am frequently updating the design of these forms, adding new tracking
categories and fields. Every month I send the updated blank workbook to the
user.

Currently every time I change anything on the form, I must go through all 62
sheets to update them. I would like to automate this by having a 'template'
sheet that I cna make changes to, then automatically redesign the 62 sheets
to that format. The catch is the sheets do have a date/shift field that is
different so I can't just copy the sheet as is.
Also, I have a summary sheet that is identical in format but sums the data
for each field from all shifts.

how would I go about doing this?

'Template' within a worksheet - change existing worksheets
 

As you say, create a template sheet.

Make changes on the template sheet.

for the things that are unique on each sheet, build those into a macro
(since you posted in programming).

then run the macro which would copy the template sheet 62 times and make the
unique additions to each of the 62 sheets, then put the formulas in a
summary sheet using a 3D range (assumed). This assumes you are not making
changes to the unique items or those changes could be implemented in the
code. Design of your template would be important in making this approach
successful.

--
Regards,
Tom Ogilvy


"Rayo K" wrote in message
...
Hello,

I have a workbook that tracks production data for an entire month. In
addition to charts and tables, it has 62 nearly identical worksheets that
serve as forms for the user to enter data for each shift (2 shifts per 31
days).

I am frequently updating the design of these forms, adding new tracking
categories and fields. Every month I send the updated blank workbook to
the
user.

Currently every time I change anything on the form, I must go through all
62
sheets to update them. I would like to automate this by having a
'template'
sheet that I cna make changes to, then automatically redesign the 62
sheets
to that format. The catch is the sheets do have a date/shift field that is
different so I can't just copy the sheet as is.
Also, I have a summary sheet that is identical in format but sums the data
for each field from all shifts.

how would I go about doing this?

'Template' within a worksheet - change existing worksheets
 

Thanks. How can I save the unique fields? Should I use code to copy them to
the clipboard and then repaste them once the sheets are changed, or is it
best to hard code them into the macro? Also I have text fields with more than
255 characters which poses an additional problem.

Thanks,
Rayo

"Tom Ogilvy" wrote:

As you say, create a template sheet.

Make changes on the template sheet.

for the things that are unique on each sheet, build those into a macro
(since you posted in programming).

then run the macro which would copy the template sheet 62 times and make the
unique additions to each of the 62 sheets, then put the formulas in a
summary sheet using a 3D range (assumed). This assumes you are not making
changes to the unique items or those changes could be implemented in the
code. Design of your template would be important in making this approach
successful.

--
Regards,
Tom Ogilvy


"Rayo K" wrote in message
...
Hello,

I have a workbook that tracks production data for an entire month. In
addition to charts and tables, it has 62 nearly identical worksheets that
serve as forms for the user to enter data for each shift (2 shifts per 31
days).

I am frequently updating the design of these forms, adding new tracking
categories and fields. Every month I send the updated blank workbook to
the
user.

Currently every time I change anything on the form, I must go through all
62
sheets to update them. I would like to automate this by having a
'template'
sheet that I cna make changes to, then automatically redesign the 62
sheets
to that format. The catch is the sheets do have a date/shift field that is
different so I can't just copy the sheet as is.
Also, I have a summary sheet that is identical in format but sums the data
for each field from all shifts.

how would I go about doing this?

'Template' within a worksheet - change existing worksheets
 

I would suggest that giving a definitive answer would require much greater
knowledge of you worksheets than I have. As a thought, however, let me
point you to John Walkenbach's page on creating custom menus:

http://www.j-walk.com/ss/excel/tips/tip53.htm

I suggest this as an idea for an approach which might be applicable to you
(not to the specifics of the page). You could set up a sheet with data that
describes the unique fields, then build code that will go through this sheet
and implement the unique fields on the specific sheets.


I would avoid hard coding much that is specific in code as that would be
even more difficult to maintain I would think. Data driven approaches are
usually best. The idea of copying from existing sheets is really a
specialized version of a data driven approach.

--
Regards,
Tom Ogilvy


"Rayo K" wrote in message
...
Thanks. How can I save the unique fields? Should I use code to copy them
to
the clipboard and then repaste them once the sheets are changed, or is it
best to hard code them into the macro? Also I have text fields with more
than
255 characters which poses an additional problem.

Thanks,
Rayo

"Tom Ogilvy" wrote:

As you say, create a template sheet.

Make changes on the template sheet.

for the things that are unique on each sheet, build those into a macro
(since you posted in programming).

then run the macro which would copy the template sheet 62 times and make
the
unique additions to each of the 62 sheets, then put the formulas in a
summary sheet using a 3D range (assumed). This assumes you are not
making
changes to the unique items or those changes could be implemented in the
code. Design of your template would be important in making this
approach
successful.

--
Regards,
Tom Ogilvy


"Rayo K" wrote in message
...
Hello,

I have a workbook that tracks production data for an entire month. In
addition to charts and tables, it has 62 nearly identical worksheets
that
serve as forms for the user to enter data for each shift (2 shifts per
31
days).

I am frequently updating the design of these forms, adding new
tracking
categories and fields. Every month I send the updated blank workbook
to
the
user.

Currently every time I change anything on the form, I must go through
all
62
sheets to update them. I would like to automate this by having a
'template'
sheet that I cna make changes to, then automatically redesign the 62
sheets
to that format. The catch is the sheets do have a date/shift field
that is
different so I can't just copy the sheet as is.
Also, I have a summary sheet that is identical in format but sums the
data
for each field from all shifts.

how would I go about doing this?