VBA Excel Range Copy and Function Loop

Question

I'm looking for help with some VBA Excel code to do some merging of various sheets into a workbook and during the copy perform some worksheet functions (e.g.). I have dozens for *.csv files, in the same directory that have a single worksheet, with the same name as the file (the file is hyatt.csv and the sheet name is "hyatt"). I want to create a new sheet in the workbook that I'm in and copy a range of data (i.e. Range(A33:FA500) from those *.csv files to a new worksheet. If possible I'd also like to run some Application.WorksheetFunctions like Min, Max, Average, StDev on those ranges and write them to some new cells.

My code looks like this and copies files no problem, but the range looping with the sheet name has me stuck and I don't know if I can/should next the loop for the worksheet functions.

Sub ConsolidatedWorkbooks()
   Path = "C:\Users\emilo\Documents\airport\"
   Filename = Dir(Path & "*.csv")
   Do While Filename <> ""
       Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
       For Each Sheet In ActiveWorkbook.Sheets
           Sheet.Copy After:=ThisWorkbook.Sheets(1)
       Next Sheet
       Workbooks(Filename).Close
       Filename = Dir()
    Loop
End Sub

Separate code for functions

Sub TestAverage()
   Sheets("Sheet1").Range("b2") = Application.WorksheetFunction.Min(Sheets("worksheet").Range("b2:fa2"))
   Sheets("Sheet1").Range("c2") = Application.WorksheetFunction.Max(Sheets("worksheet").Range("b2:fa2"))
   Sheets("Sheet1").Range("d2") = Application.WorksheetFunction.Average(Sheets("worksheet").Range("b2:fa2"))
   Sheets("Sheet1").Range("e2") = Application.WorksheetFunction.StDev(Sheets("worksheet").Range("b2:fa2"))
End Sub

Answer 1

I'm not sure if I understand what you want correctly. Anyway, maybe you want to try this :

Sub test()
Dim arr() As Variant
Dim Path As String
Dim Filename As String
Dim el

   Path = "C:\Users\emilo\Documents\airport\"
   Filename = Dir(Path & "*.csv")
   
   Do While Filename <> ""
   
        ReDim Preserve arr(x)
        arr(x) = Replace(Filename, ".csv", "")
        x = x + 1
   
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        ActiveSheet.Copy After:=ThisWorkbook.Sheets(1)
        Workbooks(Filename).Close
        Filename = Dir()
       
    Loop
    
    For Each el In arr
        Sheets("Sheet1").Range("b2") = Application.WorksheetFunction.Min(Sheets(el).Range("b2:fa2"))
        Sheets("Sheet1").Range("c2") = Application.WorksheetFunction.Max(Sheets(el).Range("b2:fa2"))
        Sheets("Sheet1").Range("d2") = Application.WorksheetFunction.Average(Sheets(el).Range("b2:fa2"))
        Sheets("Sheet1").Range("e2") = Application.WorksheetFunction.StDev(Sheets(el).Range("b2:fa2"))
    Next
    
End Sub

What the Sub do :

1. while opening the csv file, it collects the name of the filename to an array (since the file name will be the name of the sheet).
2. once it finish copying all the csv files into the workbook, it loops to each item in the array as the sheet name, do your function and put the result on the active workbook sheet "Sheet1" name.

Not tested though.