Merge data from other Excell files by copying (only) data into Master matching to Headers.

Hei to all

I have the below code to merge the excel files. It works perfect.

However I have small issue with this code.
When I run, it copy both data and the header from other file, and replaces the headers in Master file. Thus I am loosing some header data in master file.

Master file have all headers and I need to copy only the data from other files and paste in the master file matching to the headers.
Other data files have limited headers with data. I need to pull only data from those files and paste to the column heads in the master file, which matches to the header.

Please, any help is highly appreciable.

Thank you very much.
paulc

Sub test()
    Dim fn As String, n As Long, x(), myRows As Long, i As Long
    Dim ws As Worksheet, AL As Object
    Application.ScreenUpdating = False
    fn = Dir(ThisWorkbook.Path & "\*.xls")
    Set AL = CreateObject("System.Collections.ArrayList")
    Do While fn <> ""
        If fn <> ThisWorkbook.Name Then
            With Workbooks.Open(ThisWorkbook.Path & "\" & fn)
                For Each ws In .Worksheets
                    n = n + 1: ReDim Preserve x(1 To n)
                    x(n) = ws.Range("a4", ws.Cells.SpecialCells(11)).Value
                    For i = 1 To UBound(x(n), 2)
                        If (x(n)(1, i) <> "") * (Not AL.Contains(x(n)(1, i))) Then AL.Add x(n)(1, i)
                    Next
                    myRows = myRows + UBound(x(n), 1)
                Next
                .Close False
            End With
        End If
        fn = Dir
    Loop
    Application.ScreenUpdating = True
    If n = 0 Then Exit Sub
    AL.Sort: GetData x, AL, myRows
End Sub

Private Sub GetData(x, AL, myRows)
    Dim a, i As Long, ii As Long, iii As Long, n As Long, flg As Boolean
    ReDim a(1 To myRows, 1 To AL.Count): n = 1
    For i = 0 To AL.Count - 1
        a(n, i + 1) = AL(i)
    Next
    For i = 1 To UBound(x)
        For ii = 2 + IIf(flg, 1, 0) To UBound(x(i), 1)
            If x(i)(ii, 1) <> "" Then
                n = n + 1
                For iii = 1 To UBound(x(i), 2)
                    a(n, AL.IndexOf_3(x(i)(1, iii)) + 1) = x(i)(ii, iii)
                Next
            End If
        Next
        flg = True
    Next
    With Sheets("Summary").[a4].Resize(n, AL.Count)
        .CurrentRegion.ClearContents
        .Value = a
    End With
End Sub

Here is my shot at it.

On the control panel sheet you identify two directories:
- Cell B1 is the "in Basket" - enter the path name of the folder containing the files to be processed.
- Cell B2 is the "Out Basket" - enter the path name of the folder where you want the files to be placed after they are processed.

Clear the data on the summary sheet rows 7 and below if you wish and click the button. Data will be appended unless it is cleared manually.

Here is the same file modified (not tested because I no longer had the test data), Instead of popping up a dialog box on not found, it writes them to a log sheet called "Not Found."