How can I create master sheet from a couple sheets with different column arrangements?-CodePudding

I am given one excel workbook with multiple sheets. Within each sheet, there are n rows, one for each client, and the different columns represent different information about each client.

The issue is that I am tasked with creating one big sheet with all the info from all the sheets I am given, but the columns are not all in the same order, and some sheets don't have all the columns. I can't create a loop that takes each sheet and copies it at the bottom of the master sheet, since the columns would not be aligned, both due to their order and the fact that some are missing from some sheets.

All columns in all sheets have the same names, as in the "name" column might not be in the same place but will always have the same header. Same with data types and format, they are not in same order and not all of them are present but for each sheet, the ones that exist have the same data type and format.

Edit: this is an example of the data I have made myself to not use real data with client's names and information. Row 1 is the headers in string format ("a", "b", etc) and the rest are placeholder numbers.

[ enter image description here

The image above are data in each sheet with the header.
(sh1 = data in sheet1, sh2 = data in sheet2, and so on).

From the image example, the header which has the most columns are in sheet4. So, this header will be the main header of the output sheet.

The expected result in sheet named "Output" is like the image below :

If that's what you mean :

Sub test()
Dim rg As Range: Dim cell As Range
Dim sh As Worksheet: Dim ws As Worksheet
Dim cnt As Long: Dim cc As Long: Dim LR As Long

cnt = 0

For Each sh In Sheets
    With sh
        cc = .Range("A1", .Range("A1").End(xlToRight)).Columns.Count
        If cc > cnt Then cnt = cc: Set ws = sh
    End With
Next

With Sheets.Add
    .Name = "Output"
    ws.Range("A1", ws.Range("A1").End(xlToRight)).Copy Destination:=.Range("A1")
    Set rg = .Range("A1", .Range("A1").End(xlToRight))
End With

For Each sh In Sheets
    If sh.Name <> "Output" Then
        LR = ActiveSheet.UsedRange.Rows.Count   1
            For Each cell In rg
            Set c = sh.Rows(1).Find(cell.Value, lookat:=xlWhole)
            If Not c Is Nothing Then Cells(LR, cell.Column).Resize(Range(c.Offset(1, 0), c.End(xlDown)).Rows.Count, 1).Value = Range(c.Offset(1, 0), c.End(xlDown)).Value
        Next
    End If
Next

End Sub

There are three loops in the sub.
The first loop is to get the header with the most columns used.
So in this case, the ws variable in this first loop will be sheet4 (6 col header).

Then it create a new sheet, name it as "Output",
copy the header in ws and paste it as sheet "Output" header,
then it make a range for the header in sheet "Output" as rg variable.

The second loop is to loop each sheet which is not named "Output",
make a variable LR to find the last row used in sheet "Output" 1,
then inside this second loop is the third loop which to loop each header column name in rg.

To each header column name in rg, it will check, if this header name is in the looped sheet (the c variable) then it will copy the data of that header column name of the looped sheet and paste it to the same header column name on the "Output" sheet.