MARCH 2020 PCWorld 129do it manually. Excel actually provides a way
to modify all your spreadsheets at once, but
this task is unreliable when sorting.
Follow the Record Macro instructions (4,
5, 6 under Macro1 above) to create this next
macro. Name the macro Macro2 and use
Ctrl+ Shift- M for the shortcut (you can
create a button on the Ribbon menu later).
This macro affects all the spreadsheets in
the MasterDB, so ensure this file is open
and active.
- Press Ctrl+ Home [to move cursor to
A1]. - Press the Down arrow key three times.
- Press Shift- End- Down- End- Right
[Hold down the Shift key, press the End key
and release, press the Down Arrow and
release, press the End key and release, press
the Right arrow and release]. - Select Data > Sort. In the Sort dialog,
choose Model Number from the drop-down
list in the Sort By field box, then choose
Values from the Sort On field box, and then
choose A-Z from the Sort Order field box,
and click OK. - Press Ctrl+ Home.
- Click the next tab at the bottom to
access the next spreadsheet (i.e., Chicago
after Boston), and repeat all steps above:
1-6, and then continue with the following
instructions below. Remember, the macro is
recording through all these steps. - Click the Master spreadsheet tab,
press Ctrl+ Home. - Select the Developer tab (from Ribbon
menu) and click Stop Recording or press
Alt+ T+ M+ R. - Save the Master file, MasterDB.
- With cursor still in MasterDB
spreadsheet, run the macro: Press Ctrl+
Shift+ M.
Macro3: Enter formulas
The formulas for the individual stores’
spreadsheets are already in place. You
entered those back in step #9 of the Prep
Work section above. These formulas are for
the Master spreadsheet, which calculates all
the others and combines the grand totals
into one “master” sheet. We use a macro for
this process rather than doing it
manually 12 times.
Follow the Record Macro instructions (4,
5, 6 under Macro1 above) to create this next
macro. Name the macro Macro3 and use
Ctrl+ J for the shortcut (you can create a
button on the Ribbon menu later). This
macro affects all the spreadsheets in the
MasterDB, so ensure this file is open and
active.- Press Ctrl+ Home [to move cursor to
A1]. - Press Down- Right- Right.
- =SUM(Boston:Denver!C2) Enter
[Enter this formula in cell C2, where the tabs
named Boston and Denver represent the first
and last spreadsheet tab names in your
workbook. This is excluding the Master, of