'Template' within a worksheet - change existing worksheets

[Rayo K]

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?

 

[Tom Ogilvy]

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.

 

[Rayo K]

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]

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.