Let’s say that you have a lot of sheets in your workbook and you want to merge all the data onto a single worksheet.
If you have your data laid out in the same way on each sheet then this piece of VBA will do the trick for you. Maybe you have sales reports for different regions/products/salespeople on separate sheets, something like this :
The code allows for a header row (which is taken from the first sheet), and just copies the data off the remaining sheets.
Merging the sheets together will give you this :
Note : I used Excel's RANDBETWEEN function to generate the sales figures, arter than type them out by hand.
The merged data is copied onto a sheet called MergedData. If you want to change this sheet name, just change the value between the double quotes of the MergeSheetName variable in the VBA as shown here :
When you run the code this is what happens :
- A new sheet called MergedData is created (or whatever you want to call it). If this sheet already exists then all data on it is deleted
- The header row and data from the first sheet in the workbook is copied to the merged data sheet
- The data from all other sheets is copied to the merged data sheet
- The header row on the merged results is made BOLD
- The columns on the merged results are auto-fitted
What to Expect from the VBA
There are certain things the code does not do and it’s important to understand these so you don’t end up with unexpected results
The range to be copied must be contiguous. I use a VBA property called CurrentRegion which copies a range bounded by blank rows/columns like so :
Our active cell is B2 so the range to copy is A2 to F4. CurrentRegion will select the range starting at A1, but I'm resizing the range in VBA to exclude the first row as we've already copied that from the first sheet. We only want the header row once.
Row 6 and Column H are ignored as Row 5 and Column G are blank – these are where CurrentRegion understands the range ends.
You can read Microsoft’s explanation of CurrentRegion here
This code cannot be used on a protected worksheet, CurrentRegion does not support this.
The code pastes values and formatting. So your merged sheet will not contain any formulas
Where’s the Code?
Enter your email address below to download the sample workbook.
Download the workbook and open the VBA editor (ALT+F11) to check out the code and start merging.
What do you think?
If you need a hand adapting this to do something else for you please let me know.
This code was written as a result of a question from one of our students, Anna Reifman, thanks Anna.
I’d like to hear from you if you have a problem you think VBA could fix, or if you have your own solution to merging sheets, or even if you have adapted my code to do something else.