This Excel tutorial explains how to combine / consolidate worksheets into one worksheet.
You may also want to read:
Excel VBA combine worksheets columns into one worksheet
Excel VBA consolidate multiple workbooks into one workbook
Excel VBA Consolidate worksheets into one worksheet
In this tutorial, I will show how to consolidate worksheets into one worksheet within the same workbook using VBA. Data from different worksheets will paste to the last row (same column) of the consolidating worksheet.
User Guide – Consolidate worksheets into one worksheet
Goal: To consolidate worksheets with name that contain text “salary” to worksheet “consol” (currently not exist)
Assumption: You have three worksheets – “salary1”, “salary2″,”others”
Step 1: Alt+F11 > insert a Module > Paste the VBA code below
Public Sub consolWS() Dim dataShtNm As String 'the sheet name of source data Dim consolShtNm As String Dim consolLastRow, loopedShtLastRow, loopedShtLastCol As String Dim msgboxRslt As Integer consolShtNm = InputBox("Enter the worksheet name that you want to conlidate data in") If consolShtNm = "" Then msgboxRsltDummy = MsgBox("Action cancel", vbInformation) Exit Sub Else dataShtNm = InputBox("Enter wildcard conditions for worksheet name that you want to consolidate data from" & vbCrLf & vbCrLf & "For example, type data* to combine all worksheet with name starts with data" & vbCrLf & vbCrLf & "Type * to conslidate all worksheets except the consol sheet iteslf") If dataShtNm = "" Then msgboxRsltDummy = MsgBox("Action cancel", vbInformation) Exit Sub Else If WorksheetExists(consolShtNm) = False Then 'worksheet does not exist msgboxRslt1 = MsgBox("Worksheet '" & consolShtNm & "' not found, a new worksheet will be created now", vbOKCancel + vbExclamation) If msgboxRslt1 = 1 Then 'user confirm to create new worksheet Sheets.Add().Name = consolShtNm For Each sht In ActiveWorkbook.Worksheets If sht.Name <> consolShtNm And sht.Name Like dataShtNm Then consolLastRow = colLastRow(consolShtNm, "B") 'check the last row in consol sheet loopedShtLastRow = colLastRow(sht.Name, "A") 'check the last row in current looped sheet loopedShtLastCol = rowLastColNm(sht.Name, 1) 'check the last column in current looped sheet Sheets(sht.Name).Range("A2", loopedShtLastCol & loopedShtLastRow).Copy 'copy all data in looped sheet Sheets(consolShtNm).Activate Sheets(consolShtNm).Range("B" & consolLastRow + 1).Select ActiveSheet.Paste For i = consolLastRow + 1 To consolLastRow + loopedShtLastRow - 1 ActiveSheet.Range("A" & i).Value = sht.Name Next i End If Next sht Else 'user cancel create new worksheet msgboxRsltDummy = MsgBox("Action cancel", vbInformation) Exit Sub End If Else 'consolidation worksheet already exists msgboxRslt2 = MsgBox("Worksheet '" & consolShtNm & "' already exists, new data will be appended beginning from the last record", vbOKCancel + vbExclamation) If msgboxRslt2 = 2 Then 'user cancel append data to last record of desired worksheet dummy = MsgBox("Action cancel", vbInformation) Else For Each sht In ActiveWorkbook.Worksheets If sht.Name <> consolShtNm And sht.Name Like dataShtNm Then consolLastRow = colLastRow(consolShtNm, "B") 'check the last row in consol sheet loopedShtLastRow = colLastRow(sht.Name, "A") 'check the last row in current looped sheet loopedShtLastCol = rowLastColNm(sht.Name, 1) 'check the last column in current looped sheet Sheets(sht.Name).Range("A2", loopedShtLastCol & loopedShtLastRow).Copy 'copy all data in looped sheet Sheets(consolShtNm).Activate Sheets(consolShtNm).Range("B" & consolLastRow + 1).Select ActiveSheet.Paste For i = consolLastRow + 1 To consolLastRow + loopedShtLastRow - 1 ActiveSheet.Range("A" & i).Value = sht.Name Next i End If Next sht End If End If End If End If End Sub Public Function colLastRow(worksheetNm As String, colNm As String) As Integer colLastRow = Worksheets(worksheetNm).Range(colNm & Rows.Count).End(xlUp).Row End Function Public Function rowLastColNum(worksheetNm As String, rowNum) As Integer rowLastColNum = Worksheets(worksheetNm).Range("IV" & rowNum).End(xlToLeft).Column End Function Public Function rowLastColNm(worksheetNm As String, rowNum) As String Dim rowLastColNum As Integer rowLastColNum = Worksheets(worksheetNm).Range("IV" & rowNum).End(xlToLeft).Column rowLastColNm = Split(Cells(1, rowLastColNum).Address, "$")(1) End Function Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean On Error Resume Next WorksheetExists = (Sheets(WorksheetName).Name <> "") On Error GoTo 0 End Function
Step 2: Run the Procedure
Navigate to Developer > Macros > select consolWS > Run
Step 3: Enter consolidation worksheet name
Type consol, then press OK
Step 4: Enter worksheet name to consolidate
Since we want to consolidate only salary related worksheets, type salary* and then press OK
Note that Wildcard condition is case sensitive
Wildcard | Meaning | Example |
* | Represents one or more characters (any character) | J* any text that starts with J *J starts with any text but ends with J *J* any text that has J in the middle |
? | Represents one character (any character) | J? 2 characters that start with J ?J 2 characters that end with J ?J? 3 characters with J in the middle |
~ | Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? | J~** any text that starts with J* ~**J any text that starts with * and ends with J ~?*~* any text that starts with ? and ends with * |
Step 5: Confirm creation of new worksheet
If you already have a worksheet called “consol”, you will not see this message. Click on OK.
Result
In the “consol” worksheet, you will find the following result.
– The first column is the worksheet name, consolidated data starts from B2
– Row 1 header is intentionally left blank in case consolidated worksheets are not in same number of columns, otherwise I don’t know which worksheet header to copy
– Consolidation order is from the left worksheet to the right worksheet, in this example, worksheet “salary2” is on the left