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.
If each sheet contains that month's data and if it is laid out in the same arrangement of columns and rows then you can summarize this full year of data into a single sheet using the Consolidate tool.
To do this select an empty sheet in the workbook - add a new one if necessary - and click in it.
You can use the Table feature and its associated filters to filter a list so it shows only those items you are interested in seeing and you can create Pivot Tables to look at your data in different ways.
You can also consolidate large amounts of data into a summary form which can show you as much or as little of your data as you want to see.
On the Data tab, in the Data Tools group, click Consolidate. For example, cell B2 (in distric1.xlsx) cell B2 (in district2.xlsx) cell B2 (in district3.xlsx).
Note: if you don't check Top row and Left column, Excel sums all cells that have the same position.
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.
Sub Get Data() Dim str Where To Copy As String, str Start Cell Col Name As String Dim str List Sheet As Stringstr List Sheet = “List” On Error Go To Err H Sheets(str List Sheet). Select ‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet Set current WB = Active Workbook Do While Active Cell. If you want to learn how to develop applications like these and more, please consider joining our VBA Classes.
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.
Often we keep identically structured data in multiple spreadsheets and files.