Esta é uma solução usando o VBA:
Public Sub overview()
resultsheet = "Overview"
Dim wkb As Workbook
Dim wks, wks1 As Worksheet
Set wkb = ThisWorkbook
On Error GoTo SheetError:
Set wks1 = wkb.Sheets(resultsheet)
destrow = 1
totalcolumn = 1
totalwks = wkb.Sheets.Count
For i = 1 To totalwks
Set wks = wkb.Sheets(i) 'Iterate over all sheets
wksname = wks.Name
If wksname <> resultsheet Then 'Exclude the overview sheet
rowdata = True
columndata = True
thisrow = 2
thiscolumn = 1
totalempty = 0
While rowdata = True
If i = 1 Then 'First sheet section
a = wks.Cells(thisrow - 1, thiscolumn)
If a <> "" Then
wks1.Cells(destrow, thiscolumn) = a
thiscolumn = thiscolumn + 1
Else
If thisrow = 2 Then
totalcolumn = thiscolumn
End If
totalempty = totalempty + 1
If totalempty = totalcolumn Then
rowdata = False
End If
If thiscolumn = totalcolumn Then
thisrow = thisrow + 1
thiscolumn = 1
destrow = destrow + 1
totalempty = 0
End If
End If
Else ' Any other Sheet section
a = wks.Cells(thisrow, thiscolumn)
If a <> "" Then
rowdata = True
wks1.Cells(destrow, thiscolumn) = a
thiscolumn = thiscolumn + 1
Else
totalempty = totalempty + 1
If totalempty = totalcolumn Then
rowdata = False
End If
If thiscolumn = totalcolumn Then
thisrow = thisrow + 1
thiscolumn = 1
destrow = destrow + 1
totalempty = 0
End If
End If
End If
Wend
End If
Next i
Exit Sub
SheetError:
If Err.Number = 9 Then
createwks = MsgBox("Worksheet " & resultsheet & " doesn't exist" & vbCrLf & "Do you want to create it?", vbYesNo, Error)
End If
If createwks = 6 Then
Set wks1 = wkb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
wks1.Name = resultsheet
Resume
End If
End Sub
Você tem que abrir Macros / VBA no ThisWorkbook para inserir um módulo e colar este código no lado direito, então execute isso e verifique o conteúdo na planilha chamada Visão geral quando termina.