Consolidating multiple excel spreadsheets
Before you start: if your worksheets are identical, it's probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels. Because our worksheets are not identical, we want Excel to sum cells that have the same labels. Here is the code that can combine multiple Excel workbooks in a specified folder into a single Excel workbook: Dir returns the first file name that matches pathname.To get any additional file names that match pathname, call Dir again with no arguments.
On each sheet, on row 14 there are the column headers: S/N, Registry Nr, Surname, First Name, (empty), Father Name, Mother Name, sex, (empty), DOB.
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.
Important: The macro examples use the Last Row or Last Col function that you can find in the last section of this page.
Copy the macro(s) and function(s) in a standard module of your workbook. Cut Copy Mode = False End With End If Next Exit The Sub: Application.