One of our customers recently received 47 workbooks and needed to get data from each sheet named "data" in these files.With the Consolidation Assistant, she was able to first create a new workbook that pulled the data sheets (and renamed them so she knew where they came from) into one workbook. She then used the consolidate sheets feature to consoldate all 47 sheets into one worksheet.
Next, he used the extract feature of the Consolidation Assistant to extract values from the same cell on each sheet and place on a new worksheet, which became his database worksheet.Just close the source wookbook and do it again with the next one, until you've collected all the worksheets you care about into one large wookbook. Would you like to answer one of these unanswered questions instead?You create summary reports using data from several different workbooks. 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.Common methods to consolidate in Excel include consolidating by position, by category, by formula or by using Excel’s Pivot Table feature.