Today, I wanted to give an example of one very common problem that can be automated thanks to excel. Let’s imagine that you are a pool cleaner and you have a file with the name and emails of your clients (or addresses) in order to automatically create one file per client that can then be printed and sent. In theory, it could even be automatically emailed to each client just a few clicks.
I know for a fact that many small businesses have files such as this one and then create invoices one by one in what can be a very boring but also potentially filled with errors task. First, here is an idea of what my file looks like:
I simply created a sample invoice that you can see here:
It’s not very good looking but the goal here wasn’t exactly that either:) I have the formulas (all vlookups) in yellow just to give you an idea.
What I will do is simply have the macro go to my first sheet, go through each line and write the invoice number in cell C3. When that happens, all other details will be updated.
Then, I will simply have this file copied into a new excel, saved with a custom name. In my case, I will name it:
With one button press, all of these invoices will be created. The possibilities are endless as I could have the macro send out emails, print them, create envelope labels, etc. Here is the code that I used:
Very simple right? As you can imagine, the posssibilties are almost endless. You can download the spreadsheet here!