Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro to Modify Multiple Worksheets in the Same Manner
Hi
I am constructing a tool with ~100 sheets that will act as scorecards and 1 summary sheet which will pull the total score info from each scorecard. In order to efficiently construct this tool I could use some help with the following: 1) How can I design a macro that will modify (in any way) all of the 100 scorecards? 2) Once I have figured out #1 above, how do I reference the total on each scorecard (same cell) to the summary sheet (into different cells) without doing it manually. Any help would be greatly appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro to Modify Multiple Worksheets in the Same Manner
To modify all 100 sheets you need a macro similar to this...
'******************* dim wks as worksheet for each wks in worksheets if wks.name < "Summary" then wks.select end if next wks '******************* In order to sum all of the sheets you just need to type =Sum( - now select all of the sheets and then select the cell or cells you want and then add a close bracket... Your formula will look like =sum(Sheet2:Sheet100!A1) That being said IMO you are going about this the wrong way. Create one sheet to hold all of the data for all of the score cards. Create another sheet that allows you to extract the data for a single scorecard such that you can print or view each individual scorecard. Create another sheet that will allow you to summarize all of the scorecards. The nice thing about this method is that if you decide to change the format of your scorecard you only need to change the one sheet. You can add more reporting easily since all of your data is in one place. This idea is more similar to having a database where you store all of your data and then extract it into reports... Just my 2 cents... -- HTH... Jim Thomlinson " wrote: Hi I am constructing a tool with ~100 sheets that will act as scorecards and 1 summary sheet which will pull the total score info from each scorecard. In order to efficiently construct this tool I could use some help with the following: 1) How can I design a macro that will modify (in any way) all of the 100 scorecards? 2) Once I have figured out #1 above, how do I reference the total on each scorecard (same cell) to the summary sheet (into different cells) without doing it manually. Any help would be greatly appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make a macro to clear multiple cells from multiple worksheets? | Excel Worksheet Functions | |||
How to write a macro to modify an Excel chart | Charts and Charting in Excel | |||
Modify Macro Code Depending on Excel Version | Excel Discussion (Misc queries) | |||
comapre two list of data & arranging in a same manner | New Users to Excel | |||
Modify a Macro to Repeat for all Selected Worksheets | Excel Worksheet Functions |