Excel Macro to Modify Multiple Worksheets in the Same Manner

[sdosssdoss]

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

 

[Jim Thomlinson]

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...