Open all the workbooks you want to merge sheets from, and place the cursor at a blank cell where you want to locate the merging data, then click Data Consolidate. The tutorial shows how to use the VLOOKUP function to copy data from another worksheet or workbook, Vlookup in multiple sheets, and look up dynamically to return values from different sheets into different cells. When looking up some information in Excel, it's a rare case when all the data is on the same sheet. Combine Data from Multiple Worksheets Using Power Query When combining data from different sheets using Power Query, it’s required to have the data in an Excel Table (or at least in named ranges). If the data is not in an Excel Table, the method shown here would not work. Suppose you have four different sheets – East, West, North, and South. How to Merge Multiple Excel Worksheets into One Worksheet Through VBA. Many office men may need to merge multiple Excel Worksheets into a single master worksheet if they want to analyze or count the data quickly and easily. Sometimes they could merge the worksheets manually, but if there are numerous worksheets, merging sheets manually would be. Feb 25, 2021 Combine Sheets - merges multiple worksheets into one based on column headers, like we did a moment ago in this example. Merge Duplicates - combines duplicate rows by key columns. Consolidate Sheets - joins tables together and summarizes their data. Copy Sheets - provides 4 different ways to merge sheets in Excel.
How do I fill-in a new sheet with data from sheets 2, 3, 4 … when those sheets have different columns (or are in different order)?
Combining data with different columns? Easier than kitten mittens
Combining data from many sheets into a single sheet? Love it.
It’s something you’ll do all the time, and you can check out tutorials on how to accomplish this task when all the sheets have the same column order as well as when you need to skip certain sheets but combine others.
But what about when you have different columns on each sheet? Or when the columns share similarities, but are in different order?
It’s a pain in the ass, but by using a Scripting.Dictionary to track column names (as Keys) and numbers (as Items) you can ensure that your data lines up appropriately for an easy pivot table.
Let’s check out an example, featuring my favorite sales teams of all time: Dennis, Mac, Frank, Charlie, Sweet Dee, and Artemis from It’s Always Sunny in Philadelphia. You’ll notice that the sheets have some columns in order, some shared columns, and some NON-shared (i.e. totally different) columns:
Each sheet has similarities and differences in columns!
Before we go any further, you will need to make sure you have the Microsoft Scripting Runtime added to this project (if you have not already).
This is how to add the Microsoft Scripting Runtime Reference
This 13-second gif walks you through the steps, but in case it is not working here is a quick step-by-step guide:
Phew! Now we can get back to the task at hand… combining data!
Here’s the scoop y’all — our It’s Always Sunny sales data can be combined with this macro:
Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.
Let’s review the code using the 4-step VBA process as our guide:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
Step 1 – Setup is a cinch, and we knock it all out on lines 14-18. We:
Smooth! With our set up out of the way, we’ll accomplish Step 2 – Exploration and Step 3 – Execution in two phases:
Let’s dive into Phase 1!
The Step 2 – Exploration of Phase 1 takes place between lines 26-40.
First, we start looping through all of the Worksheets in ThisWorkbook on line 26, ignoring the Destination Worksheet (wksDst) on line 29.
Once we are sure we are NOT on the Destination Worksheet, we identify the last-occupied column on line 35 using LastOccupiedColNum from the VBA Toolbelt. You’re using the VBA Toolbelt, right? Please download it, use it as your new project template, and save yourself TONS of repetitive coding…
But let’s move on, as our Step 2 – Exploration for Phase 1 is done!
Line 36 kicks off a For…Next loop through this Worksheet’s occupied-columns, which is where our Step 3 – Execution takes place for Phase 1. Inside this loop, we will repeat the next 4 steps for each column header:
Since we are inside the For Each wksSrc In ThisWorkbook.Worksheets loop, those steps are repeated for each Worksheet as well!
The last bit of Step 3 – Execution for Phase 1 happens on lines 58-60, which is where we set up the Destination Worksheet with the header column names we just collected.
Line 58 starts by kicking off a For Each loop to iterate through each Key in dicFinalHeaders.
Finally, on line 59, we write each header column name to its appropriate column number on wksDst, our Destination Worksheet — a cinch, since dicFinalheaders(varColHeader) gives us the column number.
Boom! That wraps up Phase 1 and sets us up for an easy Phase 2 — take a moment to celebrate and enjoy this gif of Charlie shooting a gun.
Get excited like Charlie y’all, we’re almost done!
The Step 2 – Exploration in Phase 2 takes place between lines 71-85.
Much like Phase 1, we use a For Each loop on line 71 to iterate through each Worksheet, and on line 74 we make sure that the final Destination Worksheet is skipped.
So far, so good!
On lines 80 through 85, we assign three variables to make our copy / paste (which is the next step in Phase 2, Execution) work smoothly:
That wraps Step 2 – Exploration for Phase 2, which means it’s time to jump into Step 3 – Execution!
Line 90 kicks off a For loop through each of the columns on our Source Worksheet. (Remember, we repeat this for each Worksheet that is not the final Destination Worksheet, just like in Phase 1.)
Line 91 assigns strColHeader, the name of this particular column header. (We will use this name in the next step, to get the right destination column number from dicFinalHeaders.)
Lines 95-96 set rngDst, the cell target on our final Destination Worksheet, using two things:
Lines 97-98 set rngSrc, the column of data from our Source Worksheet. Since we know the column number (lngIdx, as we’re looping through the columns) as well as the last-occupied row on the Source Worksheet (lngLastSrcRowNum), we can create this Range using these cells.
And finally, the copy / paste happens on line 104, where we call the Copy method on rngSrc with a Destination parameter of rngDst.
And with that, you’re done! Time to celebrate y’all, as you have solved a seriously challenging problem in a VERY flexible way.
The last little bit of this script is our Step 4 – Cleanup, which takes place on line 115. All we’re doing here is throwing a MsgBox to the user, letting him or her know that the data has been combined. Wahoo!
Want to see this code in action? Here’s a 12-minute guide to the script, most of which is spent illustrating exactly how each column of data gets lined up appropriately on the Destination Worksheet:
Are you combining multiple Sheets with out-of-order (or completely different) columns into a single Sheet like a pro? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.
Sometimes we want to merge multiple sheets into one sheet so that we can easily analyse the data and turn it into some useful information. This articles will tell you how to merge multiple worksheets into one worksheet using VBA.
Here I have fetched some data from server that returns data into different worksheets. I have added one more sheet and named it as “Master”. Other sheet names doesn’t matter.
Now run this macro.
How to merge sheets using this VBA Macro?
And it is done. All the sheets are merged in master.
How it works?
I assume that you know the basics of object and variable creation in VBA. in the first part we have created object and variables that we will need in our operations.
Well most of the things I have explained using comments in vba code. Let’s look at the main part of this vba code.
In earlier articles we learned how to loop through sheets and how to get last row and column using vba.
Here we are looping through each sheet in main workbook using for loop.
For Each ws In wb.Worksheets
Then we exclude “master” sheet from looping, since we will be consolidating our data in that sheet.
Then we get last row and last column number.
Now next line is very important. We have done multiple operations into one line.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range('A' & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
First we form a range using startRow, startCol and lastRow and lastCol.
This loops runs for all the sheets and copies each sheets data into master sheet.
Finally, in the end of the macro we activate the mastersheet to see the output.
So yeah guys, this is how you can merge every sheet in a workbook. Let me know if you have any query regarding this VBA code or any excel topic in the comments section below.
How to loop through sheets
how to get last row and column using vba