Merge Columns from multiple Workbooks in Excel with VBA

I had an opportunity where I had several workbooks (5 to be exact) that are updated on a regular basis, daily in some cases. Each workbook has similar information but in different locations. For my usage I desired to combine the applicable columns into a single set of columns in one Excel file. A merged document will allow me to look in one location rather than five, and find all the results.

I searched and searched and wasn’t able to find anyone else who had solved my particular problem. I set out to learn a little Visual Basic For Applications (VBA) so that I could accomplish the task.

The first thing to do when looking to solve a problem for yourself is to look at This Chart. This Script shaves 15 minutes off a task that between me and the other people that use it is done almost daily. It took me less than 11 hours to get the code to work so I think I came out ahead. Hopefully this code will save you that much time in getting your solution working.

You’ll notice in the code that I reference two Rectangles. The first is used to start the VBA Script  and show when the information was last updated. The second lists the opened Workbooks and the date they were last updated. These Shapes and the associated code is optional.