The Consolidation Assistant add-in for Microsoft Excel is designed to help you consolidate data from multiple workbooks or from multiple worksheets.
Navigate this powerful tool for business, home management, technical work, and much more with the only resource you need, Excel 2016 Bible.
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 .
Goal: To consolidate worksheets with name that contain text “salary” to worksheet “consol” (currently not exist) Assumption: You have three worksheets – “salary1”, “salary2″,”others” Public Sub consol WS() Dim data Sht Nm As String 'the sheet name of source data Dim consol Sht Nm As String Dim consol Last Row, looped Sht Last Row, looped Sht Last Col As String Dim msgbox Rslt As Integer consol Sht Nm = Input Box("Enter the worksheet name that you want to conlidate data in") If consol Sht Nm = "" Then msgbox Rslt Dummy = Msg Box("Action cancel", vb Information) Exit Sub Else data Sht Nm = Input Box("Enter wildcard conditions for worksheet name that you want to consolidate data from" & vb Cr Lf & vb Cr Lf & "For example, type data* to combine all worksheet with name starts with data" & vb Cr Lf & vb Cr Lf & "Type * to conslidate all worksheets except the consol sheet iteslf") If data Sht Nm = "" Then msgbox Rslt Dummy = Msg Box("Action cancel", vb Information) Exit Sub Else If Worksheet Exists(consol Sht Nm) = False Then 'worksheet does not exist msgbox Rslt1 = Msg Box("Worksheet '" & consol Sht Nm & "' not found, a new worksheet will be created now", vb OKCancel vb Exclamation) If msgbox Rslt1 = 1 Then 'user confirm to create new worksheet Sheets. Name = consol Sht Nm For Each sht In Active Workbook. Name, "A") 'check the last row in current looped sheet looped Sht Last Col = row Last Col Nm(sht. Paste For i = consol Last Row 1 To consol Last Row looped Sht Last Row - 1 Active Sheet. Name, 1) 'check the last column in current looped sheet Sheets(sht. Range("A2", looped Sht Last Col & looped Sht Last Row).
Name, 1) 'check the last column in current looped sheet Sheets(sht. Range("A2", looped Sht Last Col & looped Sht Last Row). Copy 'copy all data in looped sheet Sheets(consol Sht Nm).