Newsletter - Janury '09

Spreadsheet Tips
Automating tasks using MACROS.

Spreadsheet Tips - Automating tasks with MACROS
As we spend more and more time at the PC nowadays, it makes sense to put it to the best use possible. Computers are very good at doing repetitive tasks, very quickly. Using macros allows you to tap into this without learning any programming. They, essentially, record what you do, and allow you to tell the computer to repeat this at the press of a button. We will go through a simple example here of re-sorting some columns of data.

Automating tasks using MACROS

The Christmas holidays have passed and at our fitness centre, Working-Out Data, it's time for January Promotions and Tax Returns. The manager, Loretta, has made a New Year's Resolution to get better at keeping on top of completing her tasks and knowing what's still left to do. She is keeping the ’To Do’ list in a spreadsheet. See Fig 1.

Fig 1



After one of the tasks is completed, Loretta changes the status to ’Done’. See Fig 2.

Fig 2



In order to be able to quickly see the tasks still ’To Be Done’, she sorts the list to put all the ’Done’ items to the bottom. First, she highlights the data to be sorted, then selects Sort from the Data menu. See Fig 3.

Fig 3



This brings up the ’Sort’ dialog box. We want to sort on ’Progress’ and in a ’Descending’ order as ’T’ comes after ’D’ in the alphabet. See Fig 4.


Fig 4



Clicking on OK sorts the data by the ’Progress’ column. We see that the ’Done’ tasks have moved to the bottom. See Fig 5.

Fig 5



Sorting data can be automated by creating a macro. Each time Loretta needs to re-sort the data she will be able to do it with one click of a button. Macros can be written using code, or, as in this example, the programme will record your actions and convert them to code for you. To record the macro we go the Tools menu, then select Macro, then Record New Macro. See Fig 6.

Fig 6



This brings up the ’Record Macro’ dialog box, as in Fig 7. We give the macro a name. Here, we have called it ’resort’. Click OK.

Fig 7



This will take us back to the spreadsheet, along with a small box which contains the ’STOP’ button. We will click this once we have finished the task we want to record. The first step in the process is to select the data to be sorted, as in Fig 8.

Fig 8



We next go up to the DATA menu and select SORT, as in Fig 9.

Fig 9



This brings up the ’Sort’ dialog box. We again select ’Progress’ and Descending, as in Fig 10, then click OK.

Fig 10



We now click the STOP button on the small box which came up when we started recording the macro, as in Fig 11.

Fig 11



Now the programme contains a macro which will sort our data in one go. Next, we add a button to the toolbar and assign the macro to it. To start this, we customise the toolbar. From the VIEW menu, select TOOLBARS, then CUSTOMIZE, as in Fig 12.

Fig 12



This brings up the ’Customize’ dialog box. Select the Commands tab, then in the left hand section scroll down to Macros and select it. In the right hand section there will then be two options, as in Fig 13. We want the Custom Button option. Click and drag the smiley face up on to the toolbar.

Fig 13



Leave the ’Customize’ dialog box open, and right-click on the smiley you just dragged on to the toolbar. At the bottom of the menu, select ASSIGN MACRO, as in Fig 14.

Fig 14



This will bring up the ’Assign Macro’ dialog box. Select the ’resort’ macro from the list, then click OK.

Fig 15



Next time one of the tasks is done, and Loretta updates the list, she only needs to click on the Smiley face button on her toolbar and the list will be automatically sorted. In Fig 16, we change the progress for ’Cancel the Milk’ to ’Done’.

Fig 16



Now clicking on the Smiley face button in the toolbar will resort the data automatically. As in Fig 17.

Fig 17




I hope that this brief introduction to macros and automation of work have helped to show their potential for automating tasks and, therefore, increasing productivity. The example we went through here was very simplistic, but macros can be used to perform very complex calculations and lengthy tasks. This idea of a To Do list is especially useful if you are adding tasks to a list for someone else, and they can quickly see what is to be done. If you need assistance with setting up or ammending macros, Working Data can be contacted by clicking here.



back to top		View our tips archive.