Sub MergeWorkbooks()
    Dim filenames() As String
    Dim i As Variant
    Dim folder, filter, wbname, files As String
    Dim newwb, extwb As Workbook
    Dim newws, extws As Worksheet
    Dim cols As Variant
    Dim col, newrow, lastrow, lastcol As Integer
    Dim rnge As Range
    
    ' Process variables
    wbname = "Merged_Country_Data.xlsx"
    folder = "C:\Data"
    filter = "*.xlsx"
    cols = Array("Source File", "Country", "EAN", "SKU Name", "Month", "Unit Price", "Volume", "Local Value")
    
    ' Create new workbook
    Set newwb = Workbooks.Add
    
    ' Store new worksheet for paste operations
    Set newws = newwb.Worksheets(1)
    
    ' Add column titles to new workbook
    For col = 1 To UBound(cols) + 1
        newws.Cells(1, col) = cols(col - 1)
    Next
    
    ' Apply formatting to new workbook
    newws.Range("C:C").NumberFormat = "#"
    newws.Range("F:F").NumberFormat = "$#,##0.00"
    newws.Range("H:H").NumberFormat = "$#,##0.00"
    
    ' Get qualifying files
    filenames = ListFiles(folder, filter)
    
    ' Initialise row counter for new workbook
    newrow = 2
    
    For Each i In filenames
        Set extwb = Workbooks.Open(folder + "\" + i)
        lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
        lastcol = Cells.SpecialCells(xlCellTypeLastCell).Column
        
        ' Populate new workbook with source file name
        Set rnge = newws.Range("A" & newrow & ":" & "A" & newrow + lastrow - 2)
        rnge.Value = folder + "\" + i
        
        ' Copy data from external workbook
        extwb.Worksheets(1).Range(Cells(2, 1), Cells(lastrow, lastcol)).Select
        Selection.Copy
        
        ' Paste into the new workbook
        newws.Range("B" & newrow).PasteSpecial Paste:=xlPasteValues
        
        ' Close the external workbook
        extwb.Close
        
        ' Increment row counter
        newrow = newrow + lastrow - 1
        'files = files + folder + "\" + i + Chr(13)
    Next
    
    ' Autofit all columns
    newws.Cells.EntireColumn.AutoFit
    newws.Range("A1").Select
    
End Sub

savename.txt