The code examples included in this Visual How To are simplified versions of code examples in that article. Count) ' Copy over the values from the source to the destination. To configure this macro, change the value that Sub Merge Selected Workbooks() Dim Summary Sheet As Worksheet Dim Folder Path As String Dim Selected Files() As Variant Dim NRow As Long Dim File Name As String Dim NFile As Long Dim Work Bk As Workbook Dim Source Range As Range Dim Dest Range As Range ' Create a new workbook and set a variable to the first sheet. Folder Path = "C:\Users\Peter\invoices\" ' Set the current directory to the the folder path. Replace the line that sets the variable Dim Last Row As Long Last Row = Work Bk. In particular, de Bruin includes code for error handling so that if you are processing many files and one fails, the code will not fail.
Consider the scenario of a small service company that creates an Excel workbook for each invoice that they create. Ch Drive Folder Path Ch Dir Folder Path ' Open the file dialog box and filter on Excel files, allowing multiple files ' to be selected. Get Open Filename( _ filefilter:="Excel Files (*.xl*), *.xl*", Multi Select:=True) ' NRow keeps track of where to insert new rows in the destination workbook. Consider the following code example that opens a workbook.
Also, de Bruin's website has several VBA Macros that cover various merge situations.
Ron de Bruin, Microsoft Office Excel MVPFrank Rice, Microsoft Corporation August 2008Applies to: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002, Microsoft Excel 2000Contents or merge the data in each workbook into a master workbook.
Moreover if there are a large number of workbooks/worksheets, then the problem is even bigger.