Category: VBA Code Snippet

Looping through worksheets is one of the most common actions performed by those who use VBA to automate Excel.  However, we don’t always want to apply a macro to every worksheet; sometimes, we only want it for each selected sheet.  Depending on the action being performed, this can cause an error. This occurs where the Read More

Since Excel 2010, it has been possible to save Excel files as PDF documents.  PDF was then and continues to be, a common file format for distributing documents. The code examples below provide the VBA options to automate the creation of PDFs from Excel.  They can be used on their own or as part of Read More

Before I start, let me share a little secret with you… I really dislike VBA arrays.  There just seem to be too many oddities in how they work.  Compared with other programming languages, VBA seems to make arrays overly complicated.  If you feel the same, then you’re in the right place.  This post contains a Read More

Protecting and unprotecting sheets is a common action for an Excel user.  There is nothing worse than when somebody, who doesn’t know what they’re doing, over types essential formulas and cell values.  It’s even worse when that person happens to be ourselves; all it takes is one accidental keypress, and suddenly the entire worksheet is Read More

Copying, pasting and moving cells are simple everyday tasks for an Excel user.  But what if something already occupies the area we want to paste to?  We have to move things into temporary locations before putting them into their final destination.  Wouldn’t it be better if there were a simple way to swap ranges, rows Read More

We can often find spreadsheets with cells containing codes that have been shortened.  So, rather than Proj003412, the cell value has been reduced to just 3412.  It’s not a big problem to extend the code back to the full version; we could achieve that with a simple formula, then copy and paste special values.  If Read More

Today, I want to share a really useful time-saving macro with you.  It’s a macro to apportion a value across cells.  Now that I’ve written it, I’m starting to gain the benefits, and hopefully, you can too. The filename for this post is 0005 Apportion value across cells.zip. When to use the macro? Let’s say you’ve Read More

I had an interesting question from a reader recently.  He wanted to save a PDF for each item in a data validation drop-down list.  In the past, when I have tackled similar tasks, I have always used a named range with all the possible values, then looped through the cells in the named range. My Read More