I am using VBA in Microsoft excel in order to export data to a single sheet. I am trying to export data sets from 3 files (three production lines) 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." Here is my code with the first method I described.

[Code]

Sub weeksumL3()

Dim b As Double Dim c As Double Dim StartDate As String

Dim beforeShift As Variant MsgBox ("Please select this week's data file for line 2") beforeShift = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSM), *.XLSM", Title:="Select File To Be Opened") If beforeShift = False Then Exit Sub

Dim actualShift As Variant MsgBox ("Please select this week's data file for line 3") actualShift = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSM), *.XLSM", Title:="Select File To Be Opened") If actualShift = False Then Exit Sub

Dim weekSum As Variant MsgBox ("Please select this week's Weekly Summary date file") weekSum = Application.GetOpenFilename(Title:="Select File To Be Opened") If weekSum = False Then Exit Sub

b = Range("C1")

Workbooks(beforeShift).Sheets("Data").Range("K4").Copy Workbooks(actualShift).Sheets("Data").Range("K4")

Range("K5") = 0

For i = 1 To b

d = Range("K4") d = d + 1

c = Range("K5") c = c + 1

Workbooks(actualShift).Sheets("Data").Range("A4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("A4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("B4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("B4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("C4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("C4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("D4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("D4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("E4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("E4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("F4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("F4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("G4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("G4").Cells(d, 1) Workbooks(actualShift).Sheets("Data").Range("H4").Cells(c, 1).Copy Workbooks(weekSum).Sheets("Data").Range("H4").Cells(d, 1)

Range("K4") = d Range("K5") = c

Next

Range("K5") = b

End Sub

[End Code]

Any help is greatly appreciated. I can post my other code with the inputbox if that is a better option as well.