In her regular series for Cambridge Network members - now in its 15th year - software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she describes how to open several workbooks with a single click (Microsoft Excel).
Tame your computer - with mighty macros
When Microsoft released Excel 2013 it dropped one functionality that I really, really miss … Save Workspace, available in version 2010 in the Window group on the View tab. It allowed you to save several workbooks you needed for a particular task in a special workspace file (*.xlw) so that when it was time to work on the job again, you just opened the workspace file and all files would be restored in the same arrangement you left them.
Like the idea? Why not record a macro that does just that? It might look daunting, but you only have to do this once and you can save yourself a lot of time in the long run.
Here’s how (in Excel 2013 and beyond):
- If necessary, add the Developer tab to the ribbon by right-clicking anywhere on the Quick Access Toolbar and selecting Customize the Ribbon. Then, on the right hand side of the window, tick the Developer check box under Main Tabs.
- On the Developer tab, in the Code group, click Record Macro.
- Type a name for the macro, such as workspace or several. (Avoid using the name Open.)
- Press TAB and hold down, say, the SHIFT key and type the letter O to assign CTRL + SHIFT + O as a keyboard shortcut to run the macro.
- Press TAB and select Personal Macro Workbook to make the macro available whenever you use Excel. (Make sure to click Save when you close down Excel and see a prompt to save the changes you made to the Personal Macro Workbook.)
- Click OK.
- Open the files you want be able to open in one go.
- On the Developer tab, in the Code group, click Stop Recording.
In future, you can use the shortcut key you assigned in step 4 to open the various workbooks.
Bonus tip: why not add the macro as a button on your Quick Access Toolbar?
1. Right-click anywhere on the Quick Access Toolbar and select Customize Quick Access Toolbar.
2. In the Excel Options dialog box, under Choose commands from, click on Macros.
3. In the left list, double-click the newly created macro.
4. Press ENTER or click on the OK button.
By the way, you can still open a workspace (*.xlw) file that was created in an earlier version of Excel.
Want to see these sort of tips in action? Why not sign up for one of the following dates:
Thursday 27 August 09:00 - 10:00 Book now
Tuesday 8 September 10:00 - 11:00 Book now
Price: £20 + VAT = £24
24 August 2020
Highly respected training company, specialising in Excel, Word, PowerPoint and Outlook. Since 2001 we have successfully developed and delivered training courses for public and private clients, large and small, across 23 countries around the world. We feel passionate about sharing the best of what we know with others and strive to help as many people as we can.