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 |
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 |
All times are GMT +1. The time now is 05:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com