Automate data pull from one file to another

[rwm]

I have a file named "Project View 2024 02 05.xlsm" where the integers are the date in the format "YYYY MM DD". On a daily basis, I need to copy a range of data from a separate file named “0205 Data All.xls” where the integers are the date in the format “MMDD”. The column headers in this file remain the same, but the number of rows vary everyday. I typically exclude the headers row and only copy over the rows of actual data. I need a macro or VBA script that will look at the month and date in the filename of the “project view” excel and pull the range of data from the “data all” file with the corresponding month and date in that filename. The “data all” files are stored in a shared drive folder separate from where the “project view” files are stored. Everyday, I make a copy of the project view file and change the date, then I get a new data all file for the same date. I’m having trouble since the filenames are dynamic and change everyday. Would appreciate any help on this!

 

[John_w]

See if this macro in your .xlsm workbook gets you started.

Change the DataAllFolder path to suit. It copies cells A2:F<last row in column A> from the first worksheet in the "MMDD Data All.xls" workbook to A1 in the first worksheet in the .xlsm workbook.

Public Sub Import_MMDD_Data_All()

    Dim DataAllFolder As String
    Dim parts As Variant
    Dim DataAllWb As Workbook
    
    DataAllFolder = "C:\path\to\Data All files\"  'CHANGE THIS
    
    If Right(DataAllFolder, 1) <> "\" Then DataAllFolder = DataAllFolder & "\"
    
    parts = Split(Split(ThisWorkbook.Name, ".")(0), " ")
    
    Set DataAllWb = Workbooks.Open(DataAllFolder & parts(3) & parts(4) & " Data All.xls")
    With DataAllWb.Worksheets(1)
        .Range("A2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Range("A1")
    End With
    DataAllWb.Close False
    
End Sub