Option Explicit
Public Sub Auto_Define_Name(myStartingDate As Date, myEndingDate As Date)
Dim p As String, f As String
Dim d As Integer, totalDays As Integer
Dim startingYear As Integer, startingMonth As Integer, startingDay As Integer
Dim yyyymm As String, mmddyy As String
Dim rowNum As Integer
p = "C:\XXXXX\XXXX\XXXXXXXXXXXXX\XXX " 'Path
f = "\This_Report_" 'File name (partial)
totalDays = DateDiff("d", myStartingDate, myEndingDate)
startingYear = Year(myStartingDate)
startingMonth = Month(myStartingDate)
startingDay = Day(myStartingDate)
For d = 0 To totalDays
yyyymm = Format(DateSerial(startingYear, startingMonth, startingDay + d), "yyyy-mm")
mmddyy = Format(DateSerial(startingYear, startingMonth, startingDay + d), "mm-dd-yy")
Workbooks.Open Filename:=p & yyyymm & f & mmddyy & ".xlsx" 'This will return an error if the user puts in spaces because of f
'The rest of the code... Going through a loop that increments the date by a day, etc.
Next d
End Sub