Consolidating excel files

Open Filename:=Path & Filename, Read Only:=True For Each Sheet In Active Workbook.

The most terrible thing is that the workbooks you need to combine contain multiple worksheets.

Open str File Name, Update Links:=False, Read Only:=True Set data WB = Active Workbook Range(str Copy Range). We open the data workbook by using the Application. Next we select the data that has been assigned to the copy range and copy to the clipboard.

Inside our loop are the 4 variables which are assigned the 1) File name, 2) Copy Range, 3) Where To Copy and 4) Which Column contains the starting cell to paste data. Once we have our first data workbook open, we assign this to the data WB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.

First, lets take a look at the consolidate data VBA code. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted. Allow the user with the option to clear data before new is pasted. Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate.

There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure). If you are new to VBA, Excel macros, go thru these links to learn more. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel. Written by Vijay Sharma Tags: advanced vba, consolidate, consolidation, copy data, downloads, Learn Excel, macros, screencasts, VBA, workbooks Home: Main Page ?

Leave a Reply