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