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.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. I have also made use of UDF (user defined function) to find the last cell in the column that we specify.Once you have the data consolidated into one workbook you can use the search features of the Consolidation Assistant to extract ranges from selected worksheets: One of our customers used the Consolidation Assistant to consolidate a survey worksheet he had sent out to over a 100 managers.The worksheet had responses scattered all over it .
What you need from a merge can vary from situation to situation. This allows us to paste data onto separate sheets within the same workbook. We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “str Where To Copy”.If you’ve got several Excel files that you need to merge (or worksheets in a workbook), you might be having a hard time working out how to do it.There are some useful features in Excel such as 'Consolidate' and 'Remove Duplicates' but these often don't quite hit the mark.
She could then use pivot tables and data filtering to analyze the data The Consolidation Assistant has several features that allow you to conveniently view your data. Another sets the same cell on all selected sheets to the upper left corner.