You need a macro. This code will create a Master worksheet and can also be
run to update the data in the Master Worksheet.
Simply create a blank worksheet call Master. The code checks the Header Row
and Header Column on each worksheet and adds the data to the Master Sheet.
After running the code the 1st time you can re-arrange the order of the
columns and rows as you like. Then when the code is run again it will keep
the same order. You don't have to clear the master sheet between runs of the
macro. If new rows or columns are added to any sheet the code will
automatically add these rows to the last row/column in the master worksheet.
Sub UpdateMaster()
Set MasterSht = Sheets("Master")
With MasterSht
'get row and column where to place new rows and columns
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
NewCol = LastCol + 1
End With
For Each sht In Sheets
If sht.Name <> MasterSht.Name Then
With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'loop through a worksheet skipping headers
For RowCount = 2 To LastRow
RowHeader = .Range("A" & RowCount)
For ColCount = 2 To LastCol
ColHeader = .Cells(1, ColCount)
Data = .Cells(RowCount, ColCount)
'now look up data in Master Sheet
With MasterSht
Set c = .Columns("A").Find(what:=RowHeader, _
LookIn:=xlValues, Lookat:=xlWhole)
If c Is Nothing Then
AddRow = NewRow
.Range("A" & AddRow) = RowHeader
NewRow = NewRow + 1
Else
AddRow = c.Row
End If
Set c = .Rows(1).Find(what:=ColHeader, _
LookIn:=xlValues, Lookat:=xlWhole)
If c Is Nothing Then
AddCol = NewCol
.Cells(1, AddCol) = ColHeader
NewCol = NewCol + 1
Else
AddCol = c.Column
End If
'put updated data into master worksheet
.Cells(AddRow, AddCol) = Data
End With
Next ColCount
Next RowCount
End With
End If
Next sht
End Sub
