Combine Data from Multiple Sheets into One Sheet with VBA in Excel

Need to combine data from many Worksheets into a single "output" Worksheet in Excel? This handy VBA tutorial has you covered.

[gabrielcortes]

Straight up this man is a genius. I've been doing this manually for years. I figured out how to combine multiple workbooks but never multiple tabs within. This is classic. Thumbs up man!!

[MarvinIgnacio]

I really want to thank you, you're a life saver. Video uploaded Feb 2016 and here's my comment dated Feb 2021.

[Sok]

This is awesome. You are a super star. I have been looking for a way to combine large data from multiple worksheets for few days. You just make my day Dan.

[kgemvp]

Hey Dan,

Lets say I have a workbook (WB1) where sheet1 lets me enter a # value (n), which will then generate n copies of a sheet from a separate workbook (WB2). So for example I enter '3' and I get 3 copies of WB2SHT1. WB1 now has 4 sheets, WB1SH1, and 3 copies of WB2SH1 (This is already done and works, everything from here on out is uncharted territory for me!). WB2SH1 has ~6 or 7 check boxes that will need to correlate with a small range of cells. Currently, the ranges for the check boxes are are in their own sheets in WB2 (sheet 2 thru 8). All the ranges are in same (A1:M12). From WB1, after entering the # of sheets of WB2SH1 I need, I will go into each copy, select the check boxes required for that sheet, and the selected ranges will populate below, starting at B11 on down (if 2 boxes are checked, the first checkbox range (ex, WB2SH2) will populate first on WB1SH2 @ B11, then the next check box range that is checked would populate directly beneath it @ B23).

My VBA skills are non-existent (user for less than 12 hours). I managed to get this far by asking people on the internet and googling.

[ryantschirhart]

Hi Dan, is there a way to do this but to do it so it pastes values and does not paste the formulas within the cells?

[riyaray]

With your code, how can I add a new column and show each tab name to each row on the "Import" tab where all consolidated data is

[TheLeotLion]

Dan,

First, great tutorial, has helped a ton.

I'm looking to consolidate the same tab "Data Tab" from multiple workbooks. Each of the separate workbooks contain several identical tabs and I would like to use a macro to generate a master data table. this video gets me part of the equation, hoping you could help me added wrinkle of multiple workbooks.

Thanks!!

[ganeshnarasimaiah]

HI, i am getting error as Sub or Function not Defined and getting highlighted "LastOccupiedRowNum" please suggest how to solve this

[undrawingend]

This has helped me so much ! If possible how would I have this to identify a specific criteria in a sheet ? I’m trying to create a report for my manager to use for quality and this has helped but it’s pulling cells with no useful data - blank fields with a formula in. Any advice would be amazing !

[reymantan]

Hi Dan. I get a Sub or Function not defined error.

[marie-lineskaff]

Hello Dan! Thank you for this video! It helped so much! How can I copy the values only, not the whole formulas and links in the cells?

[uhhitzxlo]

How can I combine multiple sheets into one sheet with VBA but only pull specific columns into the one sheeter?

[choweychoco]

Hi, thank you for this this helps me a lot. Though I have a question, What if I just only want to copy the data coming from other sheets based on my criteria.
for example I only just want to copy the data that has a "running" status on Notes

[sonu]

Hi Dan..thanks aton...but i also want to delete the extra three sheets we had before because we already combines verything into one...so just wnat to have the import sheet and delete the rest of the sheets so can u also show us how we do that?

[ramanjeet]

IZ THERE AN ONLINE WEBSITE WHERE I CAN RUN VBA IT WILL CORRECT ME ACCORDINGLY

[kryptokats]

Hello bro, can this be modify to copy only rows base only on the specific date that I wanted

[AdemolaAfolabi]

Sir, the function LastOccupiedColNum() is missing in the example. can you please help with that as well?

[satishmen]

Hi,
I need help with the blank cell issue.
Scenario: I have a sheet with a blank cell in the center of the data.
Details: If there is data from A2 to D25 and a cell B15 is blank.
Now, when I am collating the data, the VBA code stops reading at the blank cell and stops consolidating.
Query: How to copy the blank cell to the Import sheet.
The Import sheet must contain the same data as in the original sheets, be it blank or some data.
Please suggest.. and Thanks in advance.

[guilhermeagostini]

Hy Dan! Thanks a lot! I have just one question: How can I past my data as Values in the Import sheet. In my case, my sources have formulas and I do not want them as formulas, just their values ...

[ArunKumar-liur]

I tried the same scenario (template) u taught without having any data in one sheet except the header, and after I ran the code, I could see the header has been copied to the import sheet. How can I avoid the column header to be copied if any of the three sheet is empty?