Using VBA to update a file daily with data from another daily workbook

[BYahr]

I have 21 "individual" spreadsheets that are updated each day with data that has been collected into another spreadsheet template. A template is used to collect the data so it is always in the same format and the files are named based upon the date. Example: Worksheet mm.dd.yyyy (with multiple worksheets) is used to update IOA ADM.xlsx, and USF ADM.xlsx, Etc. I'm struggling with 2 things: how to code to account for the date change in the file name and best place to locate the macro button so as not to require excessive storage over time. (This happens to 21 spreadsheets every day.) I have part of the overall macro (VBA) worked out but it depends on activating to a distinct file name (i.e. "Adv MIMO Worksheet 04.19.2023.xlsm"). How do I adjust this when that file name will change each day?

 

[NoSparks]

let vba determine the date part

Sub testing()
    MsgBox "the workbook name for yesterday was" & vbLf & _
    "Adv MIMO Worksheet " & Format(Date - 1, "mm.dd.yyyy") & ".xlsm" & vbLf & vbLf & _
    "the workbook name for today is" & vbLf & _
    "Adv MIMO Worksheet " & Format(Date, "mm.dd.yyyy") & ".xlsm" & vbLf & vbLf & _
    "the workbook name for tomorrow will be" & vbLf & _
    "Adv MIMO Worksheet " & Format(Date + 1, "mm.dd.yyyy") & ".xlsm"
End Sub

 

[BYahr]

This has taken quite awhile to get working. Thanks for all the ideas and information. I have likely taken a long way around the issue but it is currently working.

Each individual spreadsheet has it's own macro tied to a button on the template worksheet. The date issue is handled by selecting the date on the worksheet, copy it to a neighboring cell and change the format from dd/mm/yyyy to dd.mm.yyyyy. Using DIM statements to identify the current day's workbook with the new format as part of the file name, then proceeding through the many steps to move data from the worksheet to the destination cells in the individual spreadsheets.

This has been 'fun' to learn. There are a few more things I can envision Excel being able to accomplish for me and I'm still working on those.

Again thank you to the forum for all the assistance. I'm sure I could not have done it without you.