Excel VBA Consolidate worksheets into one worksheet. Excel VBA combine worksheets columns into one worksheet. Consolidate multiple workbooks into one workbook. Assume that you have a lot of workbooks, each workbook contains multiple worksheets. You may want to copy all worksheets of each workbook into a master workbook. Combine Multiple Worksheets into One In this article, we are going to learn how to assemble the data from different workbooks through VBA in Microsoft Excel. Let’s understand with a simple exercise, how to merge spread sheet through VBA in Microsoft Excel.
Like Samuel L. Jackson in Jurassic Park, this one’s a classic. You and your team are sharing a single Workbook, with each of you operating on your own Sheet. Once everybody is done, you need to combine the data from each Sheet into a single, continuous Sheet for import into a different program. (Or a final pivot table. Or a report to your manager. Or a what-the-flip-ever …)
Let’s dissect a real example. Suppose you have a Workbook like this, with data Sheets that have the same headers (but different info on each). You want to combine this data onto the “Import” sheet, which has the exact same headers too.
All Sheets have the same headers but varying rows
Cool. Here’s the code that gets the job done:
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 our 4-step VBA process as a guide:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
Step 1 – Setup takes place from lines 11-13, where we make three assignments:
We know that all the data Worksheets have the same shape, so lngLastCol is going to be the same value for the duration of the script.
(Wondering about the functions LastOccupiedRowNum and LastOccupiedColNum? They’re in the VBA Toolbelt and are also defined on lines 45-87… You will use these functions constantly in Excel, so get used to defining them in your macros!)
The final setup step occurs on line 16, where we assign the initial Destination — this is where our first paste will start.
Next up is Step 2 – Exploration, which occurs inside the loop from lines 19-25. The For Each wksSrc in ThisWorkbook.Worksheets loop iterates through all Worksheets in this Workbook. (LOVE this syntax… so easy to read and understand!)
Line 22 ensures that the “Import” Worksheet is skipped (since that Worksheet is the destination, NOT a source). This phase ends on line 25, where the last occupied row on the source Worksheet is identified — again, leveraging LastOccupiedRowNum from the VBA Toolbelt.
We’re onto Step 3 – Execution! Short and sweet here, from line 28 to 31.
The source Range is assigned on line 29, taking advantage of the last row, lngSrcLastRow, which we figured out on line 25 above, and lngLastCol, which we identified wayyy back up on line 13. Finally, we use the Range.Copy method on line 30 to move the data to the destination Range — smooth!
Lines 34-35 are a quick switch back to Step 2 – Exploration, this time taking place on the destination (“Import”) Worksheet. Since new data has been added (in the Execution step above), we recalculate the last-occupied row, reset the destination range and continue the loop on to the next Worksheet.
That’s it, no Step 4 – Cleanup necessary!
Here’s a 4-minute guide to the code above, with an emphasis on the Exploration and Execution steps:
Combining multiple Sheets into one Sheet with VBA like a boss? 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.
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 time-consuming. Now I would introduce merging multiple Excel Worksheet to One Worksheet through VBA.
Now we suppose that you have three Worksheets contains some information about the students and now you would like to merge them into a Worksheet. Please follow the under steps if you are interested in learning merging multiple Excel Worksheet to one Worksheet through VBA.
Step 1: Press the ALT + F11 keys at same time to open the Microsoft Visual Basic for Applications window.
Step 2: Click Insert>>Module, then paste the under codes into the newly opened module.
Attention: If your sheet contains numerous columns of data, just set the Range in the last part of the code as big as possible.
Step 3: Press F5 or click the run icon in the toolbar. Then you would see there is a new sheet called Combined appearing in the Excel sheet line.