Category: VBA Code Snippet

Pivot Tables are a key tool for many Excel users to analyze data.  They are flexible and easy to use.  Combine the power of Pivot Tables with the automation of VBA, and we can analyze data even faster.  This post includes the essential code to control Pivot Tables with VBA. Download the example file I Read More

While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete Read More

Automating processes with VBA can involve copying, moving, deleting and managing lots of files.  Thankfully, VBA has many built-in functions to undertake these tasks for us.  These files do not necessarily need to be Excel workbooks; they can be any file type. Download the example file I recommend you download the example file for this Read More

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