VBA Blogs: Common Traps 2

Welcome to this week’s VBA blog! In line with previous months, we’re going to keep this month’s blogs on a theme, and this month’s theme is all about common errors and issues that people run into.

This week, we’re going to highlight a common issue that arises when people record macros.

Suppose I have a list of employees and the salaries that they earn. I want a macro that will copy the total salary from this table and paste it elsewhere in my worksheet.

In the picture above, we are looking to copy the total in B4 and paste it as values in E1. We can record a macro to do this.

Then, when we want to insert a new name into the list, we might run into issues…

Wait, what happened? The macro did exactly as we asked it to – go to cell B4, and paste the value into cell E1.

The trick is to make the reference to the cells dynamic instead. Instead of referring to B4, you can go to the target cell and set the Name of the cell to be something unique and relevant, for example:

Repeat the process for your target cell, then you can update your macro details accordingly:

A little clean-up goes a long way!

See you next week for more tips on how to avoid common traps!