Category: VBA Code Snippet

Visual Basic gives us access to parts of the Windows environment; therefore, there are lots of file-based activities we can achieve with VBA. One of these is to use VBA to copy a file. In this post, we look at 5 examples of how to achieve this. Copy a file VBA In this post, we Read More

When working with Excel, the worksheets order matters.  The tabs should reflect the flow which a user can understand.  Since there is no native feature to do it, using VBA to short sheets alphabetically can be a useful time-saver. But things are never as straightforward, are they? Sorting can be in standard or reverse order Read More

There is a common question that many Excel users ask: “How can I hide all sheets except one with a macro?”. So, let’s answer that very question. First, I’ll share with you the VBA to hide all sheets except one, and then the process to turn it into a reusable code block. Basic VBA code Read More

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

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

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

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. Download the example file I recommend you download the example file for this post.  Then you’ll be able Read More

To merge or not to merge?  That is the question.  And it’s a questions which divides the Excel community.  We find the Merge & Center button is on the Ribbon, so it appears to be the only option.  But soon we are greeted by these types of annoying error messages: Excel has a secret alternative Read More