How to create Excel macros and automate your spreadsheets

Use macros to combine multiple tasks into a single, one-second transaction.

1 2 Page 2
Page 2 of 2

Programming macros

Macro1: Collect and combine data

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

3. Move your cursor back to the MasterDB so it’s the active sheet.

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

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

macro shortcut key JD Sartain / IDG Worldwide

Record macro dialog box, macro shortcut_key

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

1. Move back to the BostonDB spreadsheet, then right-click the Boston tab. In the popup menu, select Move or Copy

2. In the Move or Copy dialog, check the box that says Copy.

3. In the Move Selected Sheets dialog, click the down arrow beside the To Books field box.

4. Select “MasterDB.xlsx” from the list.

5. In the second dialog: Before the Sheet, select the first spreadsheet on the list called “Master,” then click OK.

8. Excel copies the sheet and relocates your cursor to the MasterDB. Notice the new tab that says “Boston2.” Verify that the info in cell A1 shows the store number followed by a recent date (9/29/18 in this example). If yes, you’re good to go.

9. Right-click the tab of the original Boston spreadsheet and select Delete from the popup menu.

10. Excel warns in a dialog box: You Can’t Undo . . . Delete or Cancel? If you’re certain you want to remove it click Delete.Why? Because you want to replace it with the NEW Boston sheet that the Boston manager sent to you.

11. Move the Boston2 tab between the Master and Chicago tabs. If you keep the ‘2’ from Boston2, it will be easier to quickly recognize which sheets have been updated each month.

13. Click Ctrl+ Home to relocate cursor to cell A1 and re-enter this date formula: =TODAY(), (if this formula is missing), then press the Enter key

04 record macro combine data delete duplicates JD Sartain / IDG Worldwide

Record the macro, combine the data, delete duplicate sheets

Execute the Macro

1. Select the Developer tab again and click Stop Recording or press ALT+ T+ M+ R.

2. Save the Master file, then save the BostonDB file.

3. Go back to the MasterDB spreadsheet and run the macro: Press Ctrl+ M.

NOTE: Remember that the ‘+’ sign means a “simultaneous” combination keystroke; that is, Ctrl+ Shift- J means: Press and hold down the Ctrl and Shift keys with your left hand, then press the J key with your right hand, then release all three keys simultaneously. The dash (or hyphen) means a “consecutive” combination keystroke, such as End- Down, which means press the End key and release, then press the Down arrow and release. These are NOT interchangeable, so watch the signs.

4. If the macro works as expected, repeat this process again for each of the remaining 11 spreadsheets, then run the macros, save the files, and exit all spreadsheets except the Master.

NOTE: The only available shortcut keys are Ctrl+ M (which you have already used), Ctrl+ Shift- M, Ctrl+ J, and Ctrl+ Shift- J. Because shortcut keys are in short supply and the character combinations don’t make any logical sense anyway, the best solution for your mini macros are macro buttons on the Ribbon menu with names that make sense, such as Boston for the Boston macro and Dallas for the Dallas macro. Check out this other Excel macros how-to, where there’s a section with detailed instructions on how to create, name, and use macros

Macro2: Organize and sort data

This one is easy, but with so many spreadsheets, it can be a daunting task if you do 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.

1. Press Ctrl+ Home [to move cursor to A1].

2. Press the Down arrow key three times.

3. 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 all].

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

05 macro sorts the spreadsheet by model number JD Sartain / IDG Worldwide

Macro sorts the spreadsheet by Model Number

5. Press Ctrl+ Home.

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

7. Click the Master spreadsheet tab, press Ctrl+ Home.

8. Select the Developer tab (from Ribbon menu) and click Stop Recording or press ALT+ T+ M+ R.

9. Save the Master file, MasterDB.

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

1. Press Ctrl+ Home [to move cursor to A1].

2. Press Down- Right- Right

3. =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 course, because you are calculating all the values in cell C2 from the first tab Boston through the last tab Denver and entering the totals in cell C2 of the Master. Then Enter key is pressed).

4. Up arrow, Ctrl+ C [Moves cursor back up to cell C2 and copies this formula]

5. Right- Right- Shift- Right- Right- Right Enter [moves cursor to the right twice and stops on cell E2, press the Shift key and hold down while moving to the right three times, which highlights cells E2 thru H2, then press the Enter key].

06 enter calculate formulas in master for multiple sheets JD Sartain / IDG Worldwide

Enter+ calculate formulas in Master for multiple sheets

6. [While these cells are still highlighted, press] Shift- Ctrl+ 4

7. ALT+ T+ M+ R [Press these keys simultaneously or select the Developer tab and click Stop Recording].

Save, copy, and distribute

1. Ctrl+ Home

2. Save the Master file, MasterDB.

3. Send copies of the MasterDB to all store managers.

If you have any questions, follow me on Twitter and send a Tweet, or send an email.

1 2 Page 2
Page 2 of 2
  
Shop Tech Products at Amazon