Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

5 tips for creating macros perfectly in Office applications

JD Sartain | March 23, 2015
Shrink hours of labor to minutes with these easy time-saving techniques.

a. Right-click the worksheet tab of the current spreadsheet.

b. From the drop-down list, select Move or Copy Sheet.

c. From the dialog box Move Selected Sheets To Book, select (move to end) from the spreadsheet list, check the Create a Copy box, then click OK.

d. Repeat process until you have 41 copies of the first, original spreadsheet.

4. Enter Fixed Values/Data in Advance

The above example calculates the total rent dollars paid per property, then calculates the total rent dollars collected per property. Next, it calculates the difference, then provides a Profit/Loss total. This example shows how to maneuver the cursor to select ranges, cells, enter formulas, etc.

a. Only two columns/fields have unique/changing data. Column A = property name, column B = rent amount. Both are fixed values. The remaining columns are either formulas or new data. Enter the info for columns A and B in advance.

b. The first-game winner is KC. Enter the number of payments she made for each property in column C, then the number of collections she received in column E. Now record the macro to calculate the numbers.

5. Navigate with directional keys

a. Record macro: Ctrl+Home, Right 3 (press Right Arrow three times). Formula: =SUM(B1*C1). However, we need to modify it first because the formula columns--D: Total Rent Paid and F: Total Rent Collected--both use the Rent Amounts in column B. Because it's easier to copy formulas than re-enter formulas, use the Absolute/Relative Reference function key F4 to hardcode the B (absolute) in this formula so it never changes when it's copied.

Formula: Enter in cell D1: =SUM(B1 [then press F4 three times] *C1) Enter (key). Formula now looks like this: =SUM($B1*C1). Press Enter, Up.

Why Up arrow? Because the cursor moves down once (from D1 to D2) after you press Enter, and you need to copy the formula from D1.

b. Copy formula: Cursor location is D1. Type Ctrl+C (copy), Left, End-Down; Right, Shift+End-Up-Down, Enter. Wow! That's confusing. It means press the Shift key and hold throughout the entire string; that is, hold down the Shift key, then press and release the End key, press and release the Up Arrow, press and release the Down Arrow, then release the Shift key. The Shift key holds and extends the highlight, which selects the range, then the Enter key concludes and formulas are copied. The range is still highlighted, so press End-Up (to reposition the cursor in cell D1).

c. Copy again: (cursor in D1), press Shift+End-Down, Ctrl+C, Right 2, Enter, End-Up. Formulas copied and the cursor is repositioned in cell F1.

d. Calculate difference: Right, enter formula: =SUM(F1-D1) [F1 minus D1]. Press Enter. Again, type Ctrl+C, Left, End-Down; Right, Shift+End-Up-Down, Enter, Down.

 

Previous Page  1  2  3  Next Page 

Sign up for CIO Asia eNewsletters.