Using a VBA Vlookup macro when the filenames keep changing

[2vbr]

I use VLookup to copy data from column B in Spreadsheet 1 to Spreadsheet 2, and have recently made a macro to automate it.

Problem is, the filename of Spreadsheet 1 changes constantly because it's an auto-generated report that adds the current date to the end of the filename, plus a suffix for every new report generated that day:

Report2021-06-11.csv
Report2021-06-11 (1).csv
Report2021-06-11 (2).csv
Report2021-06-11 (3).csv
...etc



To get around this, I save the generated report using a generic filename (like Report.csv) and use this macro in Spreadsheet 2 to copy all the data in column B over:

      With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .SpecialCells(xlConstants).Offset(, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Report.csv'!R2C1:R1048576C2,2,FALSE)"
  End With

I'm just wondering if there's a way to cut out the "Save Spreadsheet 1 as Report.csv" step?

Saving the auto-generated Spreadsheet 1 as Report.csv does the job and allows my macro to work. I'm happy about that. It just... would be nice if there was a fancy way to amend the macro so that step could be removed from the process. Although I can't imagine how, even if Excel was told to get the data from "Currently open Spreadsheet named "Report[Today's date]+[(Highest number, if any)]" That seems a little overly complex, and I don't know if Excel can do such a thing anyway?

Is there a way to remove this step from the process? Or is this step the simplest method available, and I should just be happy it works?