Question:

Answer:

This vba code copies all values from each column header in each sheet to "consolidate" sheet. You can choose which sheets to consolidate, cell A2 and down. See picture above.

You can also choose what column headers to consolidate. Cell B1 and cells to the right. Remember column headers must be on row 1 in each sheet. Cell values don't have to be contiguous in each sheet.

VBA

Sub Consolidate()
Application.ScreenUpdating = False
'Dim
Dim csShts        As Range
Dim clmnheader        As Range
Dim sht As Worksheet
Dim LastRow As Integer
Dim i As Long
Set csShts = Worksheets("Consolidate").Range("A2")
Set clmnheader = Worksheets("Consolidate").Range("B1")
'Iterate sheet cells on sheet "consolidate"
Do While csShts <> ""
    ' Iterate all sheets to find a match between sht and csShts
    For Each sht In Worksheets
    'Find a matching sheet
    i = 0
    If sht.Name = csShts Then
        'Select sheet
        sht.Select
        'Select cell A1 on sheet
        Range("A1").Select
        'Iterate columnheaders on sheet
        Do While Selection <> ""
            'Iterate column headers on consolidate sheet
            Set clmnheader = Worksheets("Consolidate").Range("B1")
            Do While clmnheader <> ""
                'Find matching column headers on consolidate sheet
                 against column headers on current sheet
                If clmnheader.Value = Selection.Value Then
                    'Find last row in column
                    LastRow = ActiveSheet.Cells.Find(What:="*", _
                    SearchDirection:=xlPrevious, _
                    SearchOrder:=xlByRows).Row
                End If
                    'Save maximum last row number on current sheet
                    If LastRow > i Then
                        i = LastRow
                    End If
                'Move to next column header on consolidate sheet
                Set clmnheader = clmnheader.Offset(0, 1)
            Loop
        ActiveCell.Offset(0, 1).Select
        Loop
        sht.Select
        Range("A1").Select
        Set clmnheader = Worksheets("Consolidate").Range("B1")
        'Iterate columnheaders from beginning on current sheet
        Do While Selection <> ""
            Set clmnheader = Worksheets("Consolidate").Range("B1")
            'Iterate column headers on consolidate sheet
            Do While clmnheader <> ""
                If clmnheader.Value = Selection.Value Then
                    Set clmnheader = clmnheader.Offset(1, 0)
                    'Copy range
                    Do While Selection.Row <= i
                        ActiveCell.Offset(1, 0).Select
                       Selection.Copy
                        clmnheader.Insert Shift:=xlDown
                    Loop
                    ActiveCell.Offset(-i, 0).Select
                    Set clmnheader = clmnheader.Offset(-i, 0)
                    'Set clmnheader = clmnheader.End(xlUp)
                End If
                'Move to next column header on consolidate sheet
                Set clmnheader = clmnheader.Offset(0, 1)
            Loop
        'Move to next cell on current sheet
        ActiveCell.Offset(0, 1).Select
        Loop
    End If
    Next sht
'Move to next cell
Set csShts = csShts.Offset(1, 0)
Loop
Sheets("Consolidate").Select
End Sub

Now you can easily sort dates in chronological order and create a trend chart.

Get excel file

consolidate sheets.xls

(Excel 97-2003  Workbook *.xls)

Remember to backup your original excel file. You can´t undo a macro.

Useful links

Combine data from multiple sheets - Microsoft
Consolidate data in Excel and merge multiple sheets into one worksheet
Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel

Combine merge category

Merge two columns
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

Merge two columns with possible blank cells
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

Merge three columns into one list
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Excel categories

Comments (11)