Case Study

Let's try to record the macro again, using relative references. Close invoice.txt without saving changes. In the workbook MacroToimportinvoices.xis,record a new macro by choosing Tools, Macro, Record New Macro. Give the new macro a name of importinvoicesReiative and assign it a different shortcut key,such as Ctrl+j(Figure 1.14).

Figure 1.14

Getting ready to record a second try.

Figure 1.14

Getting ready to record a second try.

As you start to record the macro,go through the process of opening the invoice.txt file. Before navigating to the last row of data (using End+Down),click the Relative Reference button on the Stop Recording toolbar (refer to Figure 1.2).

Continue through the actions in the script from the case study:

1. Press the End key followed by the Down arrow to move to the last row of data.

2. Press the Down arrow one more time to move to the total row.

3. Type the word Total.

4. Press the Right arrow key four times to move to column E of the total row.

z |T| 5. Press the Autosum button and then press Ctrl+Enter to add a total to the Product Revenue column while remaining in that cell.

6. Grab the Autofill handle and drag from Column E over to Column G to copy the total formula over to Columns F and G.

7. Use Shift+Spacebar to select the entire row and apply bold formatting to it.

At this point,you need to move to Cell A1 to apply bold to the headings.You do not want the macro recorder to record the movement from Row 18 to Row 1—it would record this as moving 17 rows up,which may not be correct tomorrow. Before moving to A1,toggle the Relative Recording button off,then continue recording the rest of the macro.

8. Highlight Row 1 and hit the Bold icon to set the headings in bold.

9. Press Ctrl+A to select all cells.

10. From the menu,select Format,Column,Autofit.

11. Stop recording.

Press Alt+F11 to go to the VB Editor to review your code.The new macro appears in Module1 below the previous macro.

0 0

Post a comment