Referencing Data to an everchanging source name

[jpb104]

Hello,

I am trying to export data sets from 3 files into a new "Weekly Summary" file. However, the file names change each week to match up with the start date of the week (ex. Line 1 6-26-17). Whenever I try to reference a sheet by having the user select a file by using Application.GetOpenFileName or by having an input box where the user enters the date (ex. user enters date in inputbox "startDate", code searches for 'Workbook("Line 1" & startDate) I keep getting error messages, usually "subscript out of range." Can anyone help me with this?

 

[jpb104]

Also, I can post my exact code if that helps. But think I outlined my main issues with the examples.

 

[jpb104]

So my issue is I need to be able to change the name of the workbook.open function. So while "Line 1 6-19-17.xlsm" works and may work for a week of data, the next week the file I would need to access would be something like "Line 6-26-17.xlsm" and that code would not work. I need to find away to automatically update this so I don't need to go into my code and change the date every week