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.