Once the editor opens, add a new code module by clicking on the Insert tab at the top. Select Module to insert a new module; this is where you'll be entering the VBA macro code given below. The data sheets to be consolidated should be in another separate workbook altogether. The name of the workbook and sheets can be whatever you choose. 4 Ways to Combine Multiple Excel Files into One Workbook with Separate Sheets 1. Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets 2. Combine Multiple Excel Files into One Workbook with Paste Link Feature 3. Use Power Query to Combine Multiple Files into One Workbook with Separate Sheets 4. 
Click on the Macro dialog box to run the procedure from your Excel screen. Your combined file will now be displayed. This code has looped through each file, and copied the sheet to a new file. If any of your files have more than one sheet - it will copy those as well - including the sheets with nothing on them! Select the folder containing all your 100+ files Select the tab that contains your data You are almost there. Do your final fixes (if needed) Once you're done, click Close & Load Share Improve this answer Follow answered Jul 26, 2017 at 8:31 Sam 5,137 1 18 32 Add a comment 0 This should do what you want.
1. Merge Multiple Files into One Sheet in a New Workbook in Excel This criterion will let you know how to merge File1, File2, and File3 into one sheet in a new workbook in Excel. Steps: Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor. Go to the Ablebits Data tab > Merge group and select Copy Sheets > Selected Sheets to one Workbook while the master workbook is active. 2.Select the files (and possibly worksheets) you want to merge in the Copy Worksheets dialogue box, then click Next.. Learn more about How to Create a Drop-down List in Excel, here. Tips: 
Press ALT + F11 (or go to Developer -> Code -> Visual Basic). This will open the Visual Basic Editor. In the VB Editor, in the Project Editor, right-click on any of the objects for the workbook and go to Insert -> Module. This will insert a module for the workbook. Double click on the module. It will open the code window on the right. Macro to Combine Multiple Excel Sheets Into One Worksheet Vertically Now we'll develop a Macro to combine multiple excel files into one worksheet vertically. We'll follow the same steps as mentioned in method 1.
Open the workbook called Book1 Press Alt + F11 to the VBA page Click on Insert and then Module Copy and paste the below macro code After pasting the code, please press F5 to run the excel macro code. It will open a file and then copy the data and paste the same in Book1 and close the workbook. We can merge all the sheets of the same workbook using the power query of Excel. To do that: Step 1: First, from the Data tab, open Power Query in your worksheet like the picture below. Upon selecting the From Workbook option in the power query, you will be prompted to select your workbook. 
If iCnt > .Worksheets.Count Then ' Create or add a new sheet after the last sheet. . Sheets.Add After:=.Worksheets (.Worksheets.Count) End If. Now, you can add more files in the source folder and this Marco will add a new worksheet accordingly and merge the data in a new sheet. Here is my replacement function: Public Function GetDirectory (Optional Title As String = "Select a Folder.", Optional InitialFileName As String) As String With Application.FileDialog (msoFileDialogFolderPicker) .AllowMultiSelect = False .Title = Title .InitialFileName = InitialFileName If .Show = -1 Then GetDirectory = .SelectedItems (1) End.
That means the number of columns and their order should be the same. To merge files, you can use the following steps: First of all, extract all the files from the sample folder and save that folder at the desktop (or wherever you want to save it). Now, the next thing is to open a new Excel workbook and open "POWER Query". Steps to Combine Excel Files using VBA Open the visual basic editor (make sure to activate the developer tab if you have it on the ribbon already on the ribbon). After that, locate the current workbook from the project window (control + r) and insert a new module there. 
On the Data tab, in the Data Tools group, click Consolidate. In the Function box, click the function that you want Excel to use to consolidate the data. In each source sheet, select your data. The file path is entered in All references. When you have added the data from each source sheet and workbook, click OK. Combine by category Open the Excel file where you want to merge sheets from other workbooks and do the following: Press Alt + F8 to open the Macro dialog. Under Macro name, select MergeExcelFiles and click Run . The standard explorer window will open, you select one or more workbooks you want to combine, and click Open.