• Assuming there are no duplicate header names on each sheet.

Option Explicit

Sub Demo()
    Dim i As Long, j As Long
    Dim vKey, oDic, arrData, rngData As Range
    Dim arrRes, iR As Long, iC As Long, iRes As Long
    Dim LastRow As Long, LastCol As Long, ColCnt As Long
    Dim oSht As Worksheet, cbSht As Worksheet
    Const CB_SHT = "CombinedData"
    Const MAX_COL = 60   modify as needed
      Create CombinedData sheet
    On Error Resume Next
    Set cbSht = Sheets(CB_SHT)
    On Error GoTo 0
    If cbSht Is Nothing Then
        Set cbSht = Sheets.Add
        cbSht.Name = CB_SHT
    Else
        cbSht.Cells.Clear
    End If
    Set oDic = CreateObject("scripting.dictionary")
    iR = 2
      loop through worksheet
    For Each oSht In Worksheets
        If oSht.Name <> CB_SHT Then
            LastRow = oSht.Cells(oSht.Rows.Count, "A").End(xlUp).Row
            If LastRow > 1 Then
                LastCol = oSht.Cells(1, oSht.Columns.Count).End(xlToLeft).Column
                Set rngData = oSht.Range("A1", oSht.Cells(LastRow, LastCol))
                arrData = rngData.Value   load data into an array
                For j = LBound(arrData, 2) To UBound(arrData, 2)
                    If Not oDic.exists(arrData(1, j)) Then
                        oDic(arrData(1, j)) = oDic.Count + 1
                    End If
                Next
                ReDim arrRes(1 To UBound(arrData) - 1, 1 To oDic.Count)
                For j = LBound(arrData, 2) To UBound(arrData, 2)
                    iC = oDic(arrData(1, j))
                    For i = LBound(arrData) + 1 To UBound(arrData)
                        arrRes(i - 1, iC) = arrData(i, j)
                    Next
                Next
                  Write ouput to CombinedData sheet
                cbSht.Cells(iR, 1).Resize(UBound(arrRes), oDic.Count).Value = arrRes
                iR = iR + UBound(arrRes)
            End If
        End If
    Next
      Populate headers
    ReDim arrRes(0, 1 To oDic.Count)
    i = 0
    For Each vKey In oDic.Keys
        i = i + 1
        arrRes(0, i) = vKey
    Next
    cbSht.Cells(1, 1).Resize(1, oDic.Count).Value = arrRes
End Sub

Taller beat me to it but posting anyway...

Sub CombineTabs()
    Dim wsAll As Worksheet, wb As Workbook, ws As Worksheet, rngData As Range
    Dim header As Range, dict As Object, hdr As String, rwPaste As Long, lastRow As Long, lastCol As Long
    
    Set dict = CreateObject("scripting.dictionary")  for tracking unique headers
    dict.CompareMode = 1  vbTextCompare: case-insensitive

    Set wb = ActiveWorkbook
      Create a new sheet for the combined data
    Set wsAll = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.count))
    wsAll.Name = "CombinedData"
    rwPaste = 2  start pasting here

      Loop through all sheets in the original workbook
    For Each ws In wb.Sheets
        If ws.Name <> wsAll.Name Then
            lastRow = LastOccupiedRow(ws)
            lastCol = LastOccupiedColumn(ws)
            If lastRow > 1 Then   any data to copy?
                Set rngData = ws.Range("A1", ws.Cells(lastRow, lastCol))  all data
                For Each header In rngData.rows(1).Cells  loop over header row
                    hdr = header.Value
                    If Not dict.Exists(hdr) Then               new header?
                        dict.Add hdr, dict.count + 1           add to dictionary
                        wsAll.Cells(1, dict(hdr)).Value = hdr  add header to summary sheet
                    End If
                     copy data below header over to summary sheet
                    header.Offset(1).Resize(rngData.rows.count - 1).Copy _
                                           wsAll.Cells(rwPaste, dict(hdr))
                Next header
                rwPaste = rwPaste + lastRow - 1  increment destination row for next worksheet
            End If  any data to copy
        End If      not wsAll
    Next ws
End Sub

Function LastOccupiedColumn(ws As Worksheet) As Long
    Dim f As Range
    Set f = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If Not f Is Nothing Then LastOccupiedColumn = f.Column
End Function
Function LastOccupiedRow(ws As Worksheet) As Long
    Dim f As Range
    Set f = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not f Is Nothing Then LastOccupiedRow = f.row
End Function

Combine Worksheets With Different Headers

www.un.org/Depts/DGACM/index_spanish.htm 职衔

Option Explicit

Public DataRange As Range
Public Headers As Variant
Public RowsCount As Long
Public ColumnsCount As Long

Option Explicit

Sub CombineWorksheets()

    Const SRC_FIRST_CELL As String = "A1"   fixed (due to  CurrentRegion )
    Const DST_SHEET_NAME As String = "CombinedData"
    Const DST_FIRST_CELL As String = "A1"
    
    Application.ScreenUpdating = False
    
    Dim wb As Workbook: Set wb = ThisWorkbook   workbook containing this code
    
    Dim dsh As Object:
    
    On Error Resume Next
        Set dsh = wb.Sheets(DST_SHEET_NAME)
    On Error GoTo 0
    
    If Not dsh Is Nothing Then
        Application.DisplayAlerts = False
           dsh.Delete
        Application.DisplayAlerts = True
    End If
   
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim coll As Collection: Set coll = New Collection
    
    Dim swsp As cWorksheetProperties, sws As Worksheet
    Dim Headers() As Variant, Header As Variant
    Dim sc As Long, dc As Long

    For Each sws In wb.Worksheets
        Set swsp = New cWorksheetProperties
        With sws.Range(SRC_FIRST_CELL).CurrentRegion
            Headers = .Rows(1).Value
            swsp.RowsCount = .Rows.Count - 1
            swsp.ColumnsCount = .Columns.Count
            Set swsp.DataRange = .Resize(swsp.RowsCount).Offset(1)
        End With
        For sc = 1 To swsp.ColumnsCount
            Header = Headers(1, sc)
            If Not dict.Exists(Header) Then
                dc = dc + 1
                dict(Header) = dc
                Headers(1, sc) = dc
            Else
                Headers(1, sc) = dict(Header)
            End If
        Next sc
        swsp.Headers = Headers
        coll.Add swsp
    Next sws
    
    Dim dws As Worksheet: Set dws = wb.Sheets.Add
    dws.Name = DST_SHEET_NAME
    
    Dim drrg As Range: Set drrg = dws.Range(DST_FIRST_CELL).Resize(, dict.Count)
    drrg.Value = dict.Keys
    Set dict = Nothing
    Set drrg = drrg.Offset(1)
    
    Dim drg As Range
    
    For Each swsp In coll
        With swsp
            Set drg = drrg.Resize(.RowsCount)
            For sc = 1 To .ColumnsCount
                drg.Columns(.Headers(1, sc)).Value _
                    = .DataRange.Columns(sc).Value
            Next sc
            Set drrg = drrg.Offset(.RowsCount)
        End With
    Next swsp
    
    Application.ScreenUpdating = True
    
    MsgBox "Worksheets combined.", vbInformation
 
End Sub