Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
'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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding additional worksheets to an existing template | Excel Discussion (Misc queries) | |||
How do I apply a template to one worksheet in existing workbook? | Excel Discussion (Misc queries) | |||
Update existing excel file on template change | Excel Discussion (Misc queries) | |||
is it possible to apply template to a existing worksheet | Excel Worksheet Functions | |||
apply a template to existing worksheet | Excel Discussion (Misc queries) |