Consolidating multiple excel workbooks
The company has a folder that contains all invoices, and they want to summarize the invoice data in one workbook. NRow = 1 ' Loop through the list of returned file names For NFile = LBound(Selected Files) To UBound(Selected Files) ' Set File Name to be the current workbook file name to open. Value = File Name ' Set the source range to be A9 through C9. state and restores the value at the end of the subroutine.The invoices each have a summary box that has information in cells A9 through C9. File Name = Selected Files(NFile) ' Open the current workbook. Open(File Name) ' Set the cell in column A to be the file name. Filtering the Data Another useful example in the de Bruin article is a sample that uses a filter.Also, de Bruin's website has several VBA Macros that cover various merge situations.Consolidate According to the Position in an Excel Worksheet Identify Categories to Consolidate Excel Data Use Formulas to Consolidate Excel Data Access the Pivot Table function Community Q&A Microsoft Office Excel comes with several features for customizing tables and charts full of important data. Value ' Increase NRow so that we know where to copy data next. You may also click with the Shift key pressed to select a range of files. Count ' Close the source workbook without saving changes. Close savechanges:=False Next NFile ' Call Auto Fit on the destination sheet so that all data is readable. Use the shortcut keys Ctrl A to select all the files or click with the Ctrl key pressed to select multiple files.if you have a spelling mistake, or slightly different version of the label, it will treat them as separate).Check out our free Excel crash course to learn more tips, tricks, and best practices in Excel.
Open(Folder Path & File Name) ' Set the cell in column A to be the file name. In this case, de Bruin has created a utility for merging in Excel that is in the form of an add-in called RDB_Merge.
This section will describe how to use VBA to take the values of the summary cells from each of the invoices and put the values into a new workbook. Worksheets(1) ' Modify this folder path to point to the files you want to use. The filter enables you to limit which cells are copied into the summary workbook.
Enabling the Developer Tab You will use the Sub Merge All Workbooks() Dim Summary Sheet As Worksheet Dim Folder Path As String Dim NRow As Long Dim File Name As String 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\" ' NRow keeps track of where to insert new rows in the destination workbook. Range("A9: C9") ' Set the destination range to start at column B and ' be the same size as the source range. Once the application finds a source range, the code turns off the autofilter of its parent range.
NRow = 1 ' Call Dir the first time, pointing it to all Excel files in the folder path. Then, the code sets the range autofilter to look in a specific column for cells that match a specific criteria.
File Name = Dir(Folder Path & "*.xl*") ' Loop until Dir returns an empty string. (In his example, the cells have the value "ron".) If the cell in that column fits the criteria, the code copies the row into the destination workbook.