I am using VBA in Microsoft excel in order to export data to a single sheet. I a

Question

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.

Explanation / Answer

Below is the workaround where you could access excel using Worksheets("data").Select followed by operations that you wish and then save it as the scope is less the file will be closed once after the statements are executed so we ave it and move onto another file copy.

Dim beforeShift As Workbook

Set beforeShift = Workbooks.Open("C:UserssdffDesktopLine 16-02-17.xlsm")
Worksheets("data").Select
Worksheets("data").Range("K4").Select
RowCount = Worksheets("data").Range("K4")

Dim actualShift As Workbook

Set actualShift = Workbooks.Open("C:UserssrtrteDesktopLine 16-02-17.xlsm")
Worksheets("data").Select
Worksheets("data").Range("K4").Select
Worksheets("data").Range("L4").Value = RowCount
actualShift.Save

Dim weekSum As Workbook

Set weekSum = Workbooks.Open("C:UsersertrtDesktopLine 16-02-17.xlsm")
Worksheets("data").Select
Worksheets("data").Range("K4").Select

----your copy values to variables here

Worksheets("data").Range("L4").Value = RowCount
weekSum .Save

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.