126 PCWorld MARCH 2020HERE’S HOW HOW TO CREATE EXCEL MACROS
the Master. The Master is the spreadsheet for
the combined totals of all stores. If you are
the one who collates all the data and
executes the Master macros AND you also
manage an individual store, you must use
one of the 12 sheets you copied for your
store. The Master is for the grand totals only.- Once the branches email their
individual spreadsheets, it’s safer to copy the
individual sheets from the 12 stores’
workbooks manually.
PROGRAMMING MACROS
Macro1: Collect and combine data- Access your database folder and open
your spreadsheet titled MasterDB.xlsx
2. Open one of the new store
spreadsheets, such as the one titled
BostonDB.xlsx- Move your cursor back to the
MasterDB so it’s the active sheet. - Select the Developer tab and click
Record Macro or press ALT+ L+ R. The
Macro Name field says Macro1, and that’s a
good name. - Enter a shortcut key (if you like) in the
Shortcut_key field box (enter the letter M)
(you can create a button on the Ribbon
menu later). - In the Store Macro In field box, click
the down arrow and select Personal Macro
Workbook from the list, then click OK.
Now you are recording the macro.
Follow the instructions below, exactly,
and use your mouse to navigate around the
spreadsheet. Please note that phrases inside
square brackets are tips,
notes, and explanations of
the instructions. Do not
include these phrases or
anything they say in your
macro. - Move back to the
BostonDB spreadsheet, then
right-click the Boston tab. In
the popup menu, select
Move or Copy ... - In the Move or Copy
dialog, check the box that
says Copy. - In the Move Selected
Record macro dialog box, macro shortcut_key. Sheets dialog, click the down