Five handy Excel macros to take boring chores out of your workday

Inserting multiple sheets or copying values to make mass changes? Let’s automate them with Visual Basic for Applications (VBA).

Photo by Christian Fregnan on Unsplash

Admit it or not, we all happen to do boring tasks manually in our daily routines. Most of the time, they are repetitive and should be done in a certain pattern. There is an ocean of tools and programming languages such as Python, Alteryx, R, etc., to automate such tasks, but it is always better to carry on with the one on hand.

Yes, you guessed it right. I am talking about Visual Basic for Application (VBA) for any Microsoft Office product. Recently, I have had to insert a column to a particular sheet within more than 300 spreadsheets and it has given me the opportunity to delve into the fun VBA world of MS Excel.

Before I share these useful macros, here is a great comprehensive article about their usage and the mindset we should adapt. Also, let’s learn how to add “Developer Tab” on the ribbon in here in a concise way to do it.

Now that we have the Developer Tab ready, we can open the Visual Basic editor (Alt + F11 if you like shortcuts), add a module on the upper-left hand corner of the screen and start writing our very first macro.

How to (Un)protect all worksheets at once in all files in the folder

Protecting the excel files in common folders used by many colleagues to avoid any unintended changes can be a great idea. In our case, we are assuming that we would like to set a password for each worksheet in each file.

Below I am giving the main part of the code with the comments. For the next tasks, we will be only changing the code that we need.

How to convert all formulas into values

We all work on tables that have SUM, SUMIF, worksheets that are merged with some other data from other excel sources with the help of VLOOKUPs. When these formulas are not converted into values, it might increase the file size as well as the time for the file it takes to open.

Here is our case:

In the common folder we keep our sales data for three years. The daily sales report has 5 different worksheets and we are asked to convert all formulas into values in our daily sales reports.

How to sort worksheets alphabetically

One might argue that alphabetical sorting of worksheets doesn’t bring any added value and I’d partially agree on that. However, sorting worksheets can help us to avoid mistakes if we happen to paste values based on sheet index number. Another cool thing about this code is that it actually shows us how we state something that is not numeric in VBA.

How to insert multiple worksheet into different files

How to copy values from worksheets

We copy-pasted just six lines of code and got what we want. Easy, right? Well, this is not actually what it looks like. This requires a selection and it is a waste of processor time. It might not create problems while dealing with small data, but working with large datasets might take a lot of time.

Here is a better way to write this code:

Or even better by using the index number of the sheets.

What if they are not in order and I might be copying the wrong values? Well, remember to sort them alphabetically before using this part.

Photo by Timo Volz on Unsplash

Let’s finish the process with a bit more complex explanations. This can be tailored for different tasks and might come useful to anyone who is willing to take boring chores out of their workday.

References:

https://trumpexcel.com/excel-macro-examples/

https://www.launchexcel.com/most-common-excel-vba-commands/#3-Cells.Find-function-to-search-for-values

internal auditor/data analyst, coffee enthusiast, passionate about groove, snooker and words