Question:

I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The columns I am interested in each workshets are "Date Plan", "Date Compelted" and "variance" and "Project Code"

I then want data from all these column to be extracted in a Report worksheet and later want to do a trend chart by sorting all dates in chronological order.

Key bit to start is how do I get data out from worksheets. Esentially I want all the data without any loss. There are chances that some of the data between worksheet1 & 2 could be identical, apart from project code.

Also, preference is that this coding or formula should work for any future addition to worksheet data and workbook worksheets.

Answer:

This vba code copies all values from each column header in each sheet to "consolidate" sheet.

You can choose what sheets to consolidate, cell A2 and down. See picture below.

You can also choose what column headers to consolidate. Cell B1 and cells to the right. See picture above.

Remember column headers must be on row 1 in each sheet.

Cell values don't have to be contiguous in each sheet.

VBA

Option Explicit
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 trendchart.

Download excel file

consolidate sheets.xls

(Excel 97-2003  Workbook *.xls)

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