copy common cells and variable range from multiple worksheets to single master workbook

Put this code into a standard codemodule in your master workbook, and name one sheet of that workbook "Summary", and store that in the folder with the ESS workbooks - I have assumed they are all named ESS- somthing....

Also, create a subfolder named "Archive"

Option Explicit

Sub LoopThroughFiles()
    Dim i As Integer
    Dim strPath As String
    Dim wkbWB As Workbook
    Dim shtSource As Worksheet
    Dim shtDest As Worksheet
    Dim strAdd1 As String
    Dim strAdd2 As String
    Dim v As Variant
    Dim lngD As Long
    Dim rngC As Range
    Dim intOff As Integer
    Dim strWorkFile As String

    Set shtDest = ThisWorkbook.Sheets("Summary")

    strPath = ThisWorkbook.Path & "\"
    strAdd1 = "DG2,N11,N12,N19,N13,AO7,AO8,AO9,AO10,AO11,AO12,AO12,AO13,AO14,BO8,BO11,BO14"
    strAdd2 = "U37,AD37,AH37,DH37,C37,O37"

    strWorkFile = Dir(strPath & "ESS*")
    While strWorkFile <> ""
        Set wkbWB = Workbooks.Open(strPath & strWorkFile)
        Set shtSource = wkbWB.Worksheets("ESS FRC REQUEST")
        Set rngC = shtSource.Range("U37")
        While rngC.Value <> ""
            lngD = shtDest.Cells(shtDest.Rows.Count, "A").End(xlUp)
            v = Split(strAdd1, ",")
            For i = LBound(v) To UBound(v)
                shtDest.Cells(lngD, i + 1).Value = shtSource.Cells(v(i)).Value
            Next i

            intOff = UBound(v) + 1

            v = Split(strAdd2, ",")
            For i = LBound(v) To UBound(v)
                shtDest.Cells(lngD, i + intOff + 1).Value = shtSource.Cells(rngC.Row, Range(v(i)).Column).Value
            Next i

            Set rngC = rngC(2)
        Wend
        
        wkbWB.SaveCopyAs strPath & "Archive\" & wkbWB.Name
        wkbWB False
        Kill strPath & strWorkFile
        strWorkFile = Dir()
    Wend

End Sub

"However, this cell is populated by a formula, so i only need to find the last row with data in that cell."

What does that mean - is column B filled with formulas before values are entered into the other columns? Which column will always be filled in that would show the actual extent of the data entry?

I don't want to find the last row in column B, because it is filled with formulas and then the logic depends on the formula, and checking every cell. What column will be filled with entries that will show the last filled row? For example, if you enter values into column D for every entry, then I would use D to find the last entry.

Sub test()
    Dim strPath As String
    Dim shtS As Worksheet
    Dim shtD As Worksheet
    Dim wkbk As Workbook
    Dim strFName As String

    strPath = "C:\Users\richardson.js\Desktop\FCR DIRECTORY\SUBMITTED\"
    strFName = Dir(strPath & "*.xls*")
    While strFName <> ""
        If strFName <> ThisWorkbook.Name Then
            Set wkbk = Workbooks.Open(strPath & strFName)
            Set shtS = wkbk.Sheets("SKU Information")
            Set shtD = ThisWorkbook.Sheets("FCR Summary")
            Intersect(shtS.Cells(shtS.Rows.Count, "X").End(xlUp).EntireRow, shtS.Range("B:AG")).Copy _
                    shtD.Cells(shtD.Rows.Count, "B").End(xlUp)(2)
            wkbk.SaveCopyAs "C:\Users\richardson.js\Desktop\FCR DIRECTORY\ARCHIVE\" & wkbk.Name
            wkbk.Close False
            Kill strPath & strFName
        End If
        strFName = Dir()
    Wend

End Sub

No, I did not forget - the .Copy _ Shtd..... is what does that. This will disable macros...

Sub test()
    Dim strPath As String
    Dim shtS As Worksheet
    Dim shtD As Worksheet
    Dim wkbk As Workbook
    Dim strFName As String

    strPath = "C:\Users\richardson.js\Desktop\FCR DIRECTORY\SUBMITTED\"
    Set shtD = ThisWorkbook.Sheets("FCR Summary")

    Application.EnableEvents = False
    strFName = Dir(strPath & "*.xls*")
    While strFName <> ""
        If strFName <> ThisWorkbook.Name Then
            Set wkbk = Workbooks.Open(strPath & strFName)
            Set shtS = wkbk.Sheets("SKU Information")
            Intersect(shtS.Cells(shtS.Rows.Count, "X").End(xlUp).EntireRow, shtS.Range("B:AG")).Copy _
                    shtD.Cells(shtD.Rows.Count, "B").End(xlUp)(2)
            wkbk.SaveCopyAs "C:\Users\richardson.js\Desktop\FCR DIRECTORY\ARCHIVE\" & wkbk.Name
            wkbk.Close False
            Kill strPath & strFName
        End If
        strFName = Dir()
    Wend

    Application.EnableEvents = True

End Sub

Try this version..

Sub Test2()
    Dim strPath As String
    Dim shtS As Worksheet
    Dim shtD As Worksheet
    Dim wkbk As Workbook
    Dim strFName As String

    strPath = "C:\Users\richardson.js\Desktop\FCR DIRECTORY\SUBMITTED\"
    Set shtD = ThisWorkbook.Sheets("FCR Summary")

    Application.EnableEvents = False
    strFName = Dir(strPath & "*.xls*")
    While strFName <> ""
        If strFName <> ThisWorkbook.Name Then
            Set wkbk = Workbooks.Open(strPath & strFName)
            Set shtS = wkbk.Sheets("SKU Information")
            shtS.ListObjects("TABLE_SKU_INFORMATION").DataBodyRange.Copy
            shtD.Cells(shtD.Rows.Count, "B").End(xlUp).PasteSpecial xlPasteValues
            wkbk.SaveCopyAs "C:\Users\richardson.js\Desktop\FCR DIRECTORY\ARCHIVE\" & wkbk.Name
            wkbk.Close False
            Kill strPath & strFName
        End If
        strFName = Dir()
    Wend

    Application.EnableEvents = True