From the "Employees" worksheet, create a macro named "Header". Store the macro in the current workbook. Configure the macro to insert the "Sheet Name" in the left header cell of the active page and th

Excel Macro: "Header" - Explanation Overall Goal This task tests your ability to automate repetitive page setup formatting using a macro. By recording a macro named "Header" stored in the current workbook, any user can run it to instantly apply consistent header formatting - the

Create advanced formulas and macros

Question

From the "Employees" worksheet, create a macro named "Header". Store the macro in the current workbook. Configure the macro to insert the "Sheet Name" in the left header cell of the active page and the "Page Number" in the right header cell.

Exhibit

Explanation

Excel Macro: "Header" - Explanation

Overall Goal

This task tests your ability to automate repetitive page setup formatting using a macro. By recording a macro named "Header" stored in the current workbook, any user can run it to instantly apply consistent header formatting - the sheet name on the left and the page number on the right - across the active sheet's printed pages. This is a common professional workflow for multi-sheet workbooks that need standardized print layouts.

Step-by-Step Breakdown

Step 1: Navigate to the "Employees" worksheet

The question specifies starting from this sheet. The macro records actions against the active sheet's page setup, so being on the correct sheet ensures the macro is recorded in the right context. If you record from the wrong sheet, the macro may reference the wrong sheet's settings or behave unexpectedly.

Step 2: Open the Record Macro dialog

Go to View tab → Macros → Record Macro (or Developer tab → Record Macro).

This opens the configuration dialog before any actions are recorded. You must configure the macro's name and storage location before clicking OK, because once you click OK, recording begins immediately and every subsequent action becomes part of the macro.

Step 3: Name the macro "Header"

In the Macro name field, type exactly Header.

Macro names must match exactly when called later. A misspelling means the macro won't be found by name. Macro names also cannot contain spaces.

Step 4: Set "Store macro in" to "This Workbook"

In the Store macro in dropdown, select This Workbook.

Option	Where it lives	Availability
This Workbook	Inside the current `.xlsm` file	Only when that file is open
Personal Macro Workbook	Hidden global workbook	Available in all Excel sessions
New Workbook	A new file	Only in that file

Storing in "This Workbook" means the macro travels with the file and is scoped to it - the correct choice when the macro is meant for this workbook specifically. Choosing "Personal Macro Workbook" would be wrong here because the exam specifies the current workbook.

Step 5: Click OK to begin recording

From this point, every action you take is captured. Work efficiently and avoid extra clicks.

Step 6: Open Page Setup → Header/Footer → Custom Header

Go to Page Layout tab → click the Page Setup dialog launcher (small arrow in the corner) → Header/Footer tab → Custom Header.

This is the only place where you can insert dynamic header codes like sheet name and page number using the toolbar buttons. Using the simple built-in header dropdown presets won't give you independent left/right cell control with dynamic codes.

Step 7: Insert Sheet Name in the Left section

Click inside the Left section box, then click the Insert Sheet Name button (looks like a tab icon, inserts the &[Tab] code).

&[Tab] is a dynamic code - it automatically updates to display whatever the sheet is currently named. If you type the sheet name as plain text instead, it becomes hardcoded and won't update if the sheet is renamed.

Step 8: Insert Page Number in the Right section

Click inside the Right section box, then click the Insert Page Number button (inserts &[Page]).

&[Page] dynamically shows the current page number at print time. Putting it in the right cell specifically matches the requirement - left/center/right are distinct cells and must be set independently.

Step 9: Click OK to close the dialogs

Close Custom Header → OK → OK through Page Setup.

Step 10: Stop recording

Go to View → Macros → Stop Recording (or click the Stop button in the status bar).

If you skip this step, everything you do afterward continues to be recorded into the macro - including closing dialogs, switching sheets, or other actions - corrupting the macro's behavior.

What Goes Wrong if Steps Are Skipped

Skipped Step	Result
Wrong worksheet active	Macro may apply to wrong sheet context
Wrong storage location	Macro unavailable or stored in wrong place
Typed name instead of dynamic code	Header doesn't update dynamically
Left/right sections swapped	Sheet name and page number appear on wrong sides
Forgot to stop recording	Extra unintended actions added to macro

Memory Tip

"Name it, Store it, Do it, Stop it"

Name it - give the macro a meaningful name before recording
Store it - choose where it lives (this workbook)
Do it - perform the actions you want automated
Stop it - explicitly end recording

For the header codes specifically: think Left = Label (sheet name), Right = Running page number.

Topics

#Macro creation#Header customization#Page Setup#VBA

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice