Or if you want all the options in a nice user interface check ou this add-in : RDBMerge, Excel Merge Add-in for Excel for Windows There are a few things you must change before you can run the code Fill in the path to the folder Note: the code above use the function RDB_Last, copy this function also in your code module if you use it. It is also possible to set the start folder with Ch Drive and Ch Dir but I choose to use the Set Current Directory A function in this example because it also is working with network folders. Get Open Filename(filefilter:="Excel Files (*.xl*), *.xl*", _ Multi Select:=True) If Is Array(FName) Then For Fnum = LBound(FName) To UBound(FName) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks. Number With source Range Set destrange = destrange. Column On Error Go To 0 Case 3: On Error Resume Next lrw = rng.
If you have no idea where to paste the code then check out this page.
Where do I paste the code that I find on the internet You can also find the information from this page in this article on MSDN: Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel a zip file with a workbook with the code from this page to test the examples.
The examples will add a worksheet with the name RDBMerge Sheet to your workbook and will copy cellsfrom every worksheet in the Active Workbook to this worksheet.
Each time you run one of the examples it will first delete the summary worksheet named RDBMerge Sheet if it exists and then adds a new one to the workbook.
Note: The workbook with the code must be outside the merge folder Tip 1: Useful Workbooks. Close savechanges:=False End If Next Fnum Base Wks. Auto Fit End If Exit The Sub: You find this macro in the workbook that you can download on this page.