Applying a standard print page setup for Excel - a macro and toolbar button

This section illustrates a simple macro that can be placed in your Personal.xlsb workbook and is then available to add to a button on your quick access toolbar (or a ribbon). The macro can add a simple page footer to the current worksheet and then open the Print Preview box.

There are a large number of formatting options for headers and footers. If you record your own macro you can replace some or all of the code below. Simply modify or delete the elements which are not required.

Excel allows you to customise your ribbons and the quick access toolbar and add buttons for useful procedures and options. A macro can be linked to a new button and it then becomes available whenever Excel is opened.

Open the VBA editor ([Developer] Visual Basic Editor). Select the Personal.xlsb VBA Project and Insert | Module. Either type your own code or copy the code from this page into the module to create a new procedure called MyPageSetup().

Close the VBA editor and return to Excel. Click the small down arrow on the right hand end of the Quick Access toolbar and choose More Commands...

The Excel Options dialog box will appear and has a tab for either customising a ribbon or the [1] Quick Access toolbar. From the drop down box choose commands from [2] Macros. The macros saved in your Personal workbook will be listed. [3] Choose one of them and [4] [Add>>] it to the toolbar.

To associate the macro with a button, click on [5] [Modify...] and choose an icon picture.

Would you like to learn more?

Step by Step Excel 2010

Five keyboard short cuts

• Use <ctrl> + apostrophe (') to copy or duplicate the contents from the cell directly above. This also works in Access tables.
• Use <alt> + equals (=) to apply the AutoSum function. This places the Sum() function in the cell and Excel makes it's best guess as to the range of cells you wish to add up.
• When inputting a function use <ctrl> + <shift> + 'A' to reveal prompts for each of the required arguments. This applies when you are typing a function directly into the formula bar. For example if you type =MATCH followed by <ctrl> + <shift> + 'A', the following prompt appears: =MATCH(lookup_value, lookup_array, match_type). Edit the details, replacing each prompt word with the appropriate value.
• To switch between open workbooks press <ctrl> + <tab>.
• To add todays date into a cell, type <ctrl> + ';'
  

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.