Neville Ash asks in this post: Consolidate sheets in excel (vba)

I have down loaded the consolidated file and it does not appear to work the way I expected.

I am looking to combine cashflow worksheets for multiple projects. The row headings are the same for each project but the column heading varies (they are set as end of month dates)

The issue with normal consolidation is that each sheet has to be identical. But because each project starts and stops at different months the consolidation is messy

Answer:

I am not sure I completely understand but I gave it a try.

After clicking the "Consolidate Sheets" button:

Download excel file

Consolidate-sheets-row headers the same.xls

(Excel 97-2003  Workbook *.xls)

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

I do realize this code is not the shortest or the most efficient.

VBA code:

Option Explicit

Sub Consolidate()

Application.ScreenUpdating = False

Dim csShts        As Range

Dim clmnheader        As Range

Dim sht As Worksheet

Dim LastCol As Integer

Dim i As Long

Set csShts = Worksheets("Consolidate").Range("A2")

Set clmnheader = Worksheets("Consolidate").Range("B1")

Do While csShts <> ""

    For Each sht In Worksheets

    i = 0

    If sht.Name = csShts Then

        sht.Select

        Range("A1").Select

        Do While Selection <> ""

            Set clmnheader = Worksheets("Consolidate").Range("B1")

            Do While clmnheader <> ""

                If clmnheader.Value = Selection.Value Then

                    'Find last column in row

                    With ActiveSheet

                        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

                    End With

                End If

                    If LastCol > i Then

                        i = LastCol

                    End If

                Set clmnheader = clmnheader.Offset(1, 0)

            Loop

        ActiveCell.Offset(1, 0).Select

        Loop

        sht.Select

        Range("A1").Select

        Set clmnheader = Worksheets("Consolidate").Range("B1")

        Do While Selection <> ""

            Set clmnheader = Worksheets("Consolidate").Range("B1")

            Do While clmnheader <> ""

                If clmnheader.Value = Selection.Value Then

                    Set clmnheader = clmnheader.Offset(0, 1)

                    'Copy range

                    Do While Selection.Column <= i

                        ActiveCell.Offset(0, 1).Select

                        Selection.Copy

                        clmnheader.Insert Shift:=xlToRight

                    Loop

                    ActiveCell.Offset(0, -i).Select

                    Set clmnheader = clmnheader.Offset(0, -i)

                End If

                Set clmnheader = clmnheader.Offset(1, 0)

            Loop

        ActiveCell.Offset(1, 0).Select

        Loop

    End If

    Next sht

Set csShts = csShts.Offset(1, 0)

Loop

Sheets("Consolidate").Select

End Sub