Recently I was asked by a new client if I could help them with some statistics that they produced for their own clients. We discussed as to what they wanted to be able to do. Having linked to their computer screen remotely, we went through the process of what they did every week to produce a set of reports for a single client.  The client was copying data from one system and then pasting the information into an Excel spreadsheet, and overall took the client around 10-15 minutes, as they had to do 7 worksheets each for different countries.

166

Once the information was added on then they would update another worksheet, by copying the previous weeks row down to create the latest weeks information consolidated.  Then they would update 6 graphs showing the performance of their clients pay for click performance.

After seeing what they have been doing I advised them that it will take me about 3 hours of my time to provide him with a solution.

What was my plan.

Initial thoughts was to create an input worksheet, which would be used to automate the copy/paste routine that the client was doing.

As you can see from the above image row 17 is blank and that is where the next set of data needs to entered.  This meant that I needed to identify the next row where the data was to be entered and this row may be different of the different worksheets.  In this case they were not, however, I wrote the VBA code in such a way that if they were different it will still work.

167

Created the VBA code and tested out the routine. So with my client taking 15 minutes per client report, by pushing the button, this is now done in 3 seconds. Saving him over 14 minutes per client.

Automating certain repetitive tasks can save you time and money.  Using this example, if I was able to save 14 minutes per week for 48 weeks, which equates to 11 hours and 12 minutes in a year.  If you take an average hourly rate for an Office Administrator earning £9.34, this would be a saving of just over £102.00 in a year.  With above example, if they had to do this for 10 clients, that would be 112 hours, making the saving of over £1045.00.  This is based on 1 person doing such a task.

VBA code can be used in Microsoft Word, Microsoft Access, as well as Microsoft Excel.  If you want to learn how to automate or want to automate some of your process get in touch today and start to save time and money.

Leave Comment

Your email address will not be published. Required fields are marked *

clear formSubmit