Copy Data from many workbooks to a master using column headers

Question

Below code is working beautifully for copying data from ONE workbook to my master (Source.xlsx). Can I modify this code to do the same for many workbooks? I need data from many files in one directory to be appended to that table, using the column headers in the source. Files I am copying from always have the headers I am looking for, but in different columns. It would be super useful to save the file name that the data was copied from along with the subfolder and subfolder2 saved in added columns (master file location > subfolder > subfolder2 > files to copy from). Can you please help me StackOverflow gurus?

Sub CopyByHeader()

    Dim CurrentWS As Worksheet
    Set CurrentWS = ActiveSheet

    Dim SourceWS As Worksheet
    Set SourceWS = Workbooks("vavc1.valor.carnival.com_2017-10-30.xls").Worksheets(1)
    Dim SourceHeaderRow As Integer: SourceHeaderRow = 1
    Dim SourceCell As Range

    Dim TargetWS As Worksheet
    Set TargetWS = Workbooks("Source.xlsx").Worksheets(1)
    Dim TargetHeader As Range
    Set TargetHeader = TargetWS.Range("A1:K1")

    Dim RealLastRow As Long
    Dim SourceCol As Integer

    SourceWS.Activate
    For Each Cell In TargetHeader
        If Cell.Value <> "" Then
            Set SourceCell = Rows(SourceHeaderRow).Find _
                (Cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not SourceCell Is Nothing Then
                SourceCol = SourceCell.Column
                RealLastRow = Columns(SourceCol).Find("*", LookIn:=xlValues, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If RealLastRow > SourceHeaderRow Then
                    Range(Cells(SourceHeaderRow + 1, SourceCol), Cells(RealLastRow, _
                        SourceCol)).Copy
                    TargetWS.Cells(2, Cell.Column).PasteSpecial xlPasteValues
                End If
            End If
        End If
    Next

    CurrentWS.Activate

End Sub

Answer 1

So you need to wrap your code that works in a loop. For each workbook in folder.files, run your code.

This post will tell you how to iterate over the workbooks in a folder:

Loop through files in a folder using VBA?