Chapter 42: Introducing Visual Basic for Applications
42
FIGURE 42.9
The TimeStamp procedure was generated by the Excel macro recorder.The problem is that the macro is hard-coded to select cell F6. If you execute the macro
when a different cell is active, the code always selects cell F6 before it copies the cell. This
is not what you intended, and it causes the macro to fail.You’ll also notice that the macro recorded some actions that you didn’t make. For example, it specified several
options for the PasteSpecial operation. Recording actions that you don’t specifically make is just a by-product
of the method that Excel uses to translate actions into code.
Rerecording the macro
You can fix the macro in several ways. If you understand VBA, you can edit the code so that
it works properly, or you can re-record the macro using relative references.Activate the VBA Editor, delete the existing TimeStamp procedure, and re-record it. Before
you start recording, click the Use Relative References command in the Code group of the
Developer tab.Figure 42.10 shows the new macro, recorded with relative references in effect.