Consolidating multiple spreadsheets into one
You create a "table" for each spreadsheet to be merged You create a "query" which pulls the desired columns to one sheet Please see the Consolidate Worksheets Wizard add-in for Excel The add-in has several merge modes, one of them does exactly what you need.
Please see this link for a detailed description of the mode (how to combine sheets with the same name to one) The add-in is a shareware, but it has a 15-day fully-funtional trial version (download button at the top of the page), so you can merge thousands of your workbooks for free :) The selected worksheets will be moved or copied from the original workbook into your "Master" workbook. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Copy the macro(s) and function(s) in a standard module of your workbook. Cut Copy Mode = False End With End If Next Exit The Sub: Application.
If you have no idea where to paste the code then check out this page.
On line 38, str File Path is assigned to be the original source folder string (str Dir Containing Files, which is “C:\blog\example_data_in_here” in this example), a backslash (“\”), and the file name from col File Names.
We’ll immediately take advantage of that full file path to the Excel file on line 41, where we open that Workbook and save a reference to it as wbk Src (where “Src” is short for “Source”).
The corresponding worksheets from each Excel file are named exactly the same as are the column headers.First, we need to identify the first row of data that was just pasted in.We used this same exact row number back on line 78, so we essentially copy that logic and assign lng Dst First File Row to be lng Dst Last Row 1. Now that we know the first row and the last row of the range of cells that will need to be populated with the file name, all that’s left to do is get the right column number!Finally, the last Execution task occurs on line 117, where we actually do the file name writing. Name returns the file name (in the first case, it will be “AT_Apr_16.xlsx”), which is why we assign the rng File. 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.