Consolidating multiple excel workbooks into one
Therefore, open both Excel workbooks: The file containing the worksheets which you want to merge (the source workbook) and the new one, which should comprise all the worksheets from the separate files.The next method comes with some disadvantages and is a little bit more complicated.
Next, in the Reference box, click the Collapse Dialog button to select the data in the worksheet.'Define variables: Dim number Of Files Chosen, i As Integer Dim temp File Dialog As file Dialog Dim main Workbook, source Workbook As Workbook Dim temp Work Sheet As Worksheet Set main Workbook = Application. Active Workbook Set temp File Dialog = Dialog(mso File Dialog File Picker) 'Allow the user to select multiple workbooks temp File Dialog. Selected Items(i) Set source Workbook = Active Workbook 'Copy each worksheet to the end of the main workbook For Each temp Work Sheet In source Workbook. All you need to know is how to paste the VBA code and run it. Read More: Learn Excel Course Online | Be an Advanced Excel Master First of all, Now save the files in one Drive/Folder and link to all practice files used in our example D:\Collate Multiple Files.