Loop files based on filename and copy data from closed workbooks to active workbook

[ryan8200]

I have 4 files in a folder with filename in this format :
data__30 Sep 2021, 11_32_19.xlsx
data__30 Sep 2021, 11_32_23.xlsx
data__30 Sep 2021, 11_32_35.xlsx
data__30 Sep 2021, 11_32_48.xlsx

My objective is to copy:
1. data from files 1 and paste to active workbook A3
2. data from files 2 and paste to active workbook D3
3. data from files 3 and paste to active workbook F3
4. data from files 4 and paste to active workbook J3

The filename is not static and will be dynamic depend on the time I download the file. Is this something that can be achieved with VBA ?

 

[Marc L]

Yes but as it depends on the folder path and what is the design of each worksheet to copy (where ?) …​

 

[ryan8200]

How to change the below code so that it will automatically select first file with earliest download time, then followed by 2nd and so on ?

[/
Sub Copy

Dim WB As Workbook
Dim FileName As String
Dim A As Long, B As Long
   
FilePath = Application.GetOpenFilename(, , "Select Report")
If FilePath <> False Then

        Set WB = Application.Workbooks.Open(FilePath)
        WB.Sheets(1).Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
        WB.Sheets(1).Columns("A:A").AutoFit
        A = Range("A" & Rows.Count).End(xlUp).Row
        WB.Sheets(1).Range("A2:D" & A).Copy
        ThisWorkbook.Sheets(1).Range("D3").PasteSpecial Paste:=xlPasteValues
        Application.DisplayAlerts = False
        WB.Close False
        Application.DisplayAlerts = True
        ThisWorkbook.Sheets(1).Range("C2").Select
        Selection.End(xlDown).Select
        Selection.Cut
        B = Range("D" & Rows.Count).End(xlUp).Row
        Range("C" & B).Select
        ActiveSheet.Paste
               
End If
End Sub
]

 

[Marc L]

Just using the VBA function Dir …​