Test

Sub OpenAndCopyColumns()
    Dim folderPath As String
    Dim fileName As String
    Dim sourceWorkbook As Workbook
    Dim destWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim destWorksheet As Worksheet
    Dim i As Integer

    ' Set folder path and destination workbook/worksheet
    folderPath = "C:\YourFolderPath\" ' Change this to the folder containing the files
    Set destWorkbook = ThisWorkbook
    Set destWorksheet = destWorkbook.Worksheets("Sheet1") ' Change this to the destination worksheet

    ' Loop through 6 files in the folder
    fileName = Dir(folderPath & "*.xlsx") ' Change the file extension if needed
    i = 1
    Do While fileName <> "" And i <= 6
        ' Open source workbook and set source worksheet
        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)
        Set sourceWorksheet = sourceWorkbook.Worksheets(1) ' Change this to the source worksheet

        ' Call the CopyColumnsByHeaders function to copy columns
        Call CopyColumnsByHeaders(sourceWorkbook, sourceWorksheet, destWorkbook, destWorksheet, 1, "Header1", "Header2", "Header3") ' Change the header names as needed

        ' Close the source workbook
        sourceWorkbook.Close SaveChanges:=False

        ' Move to the next file
        fileName = Dir
        i = i + 1
    Loop
End Sub