Semi new to VBA and my code is throwing run time error 424 object required. I realize is not the most efficient code, but that doesn't matter to me so long as it works. Unfortunately it's not working. I wish that this error gave me the option to debug and see what line is causing it, but oh well.
My code is essentially opening 7 workbooks with dates in the file name that are the last seven days then copying data from each to a single workbook. I'm just trying to save a co worker a chunk of his time and learn a little more about vba. My code is listed below.
Sub DailyReportCopyPaste()
Dim MonDateYear As String
Dim MonDateMonth As String
Dim MonDateDay As String
Dim SunDateYear As String
Dim SunDateMonth As String
Dim SunDateDay As String
Dim SatDateYear As String
Dim SatDateMonth As String
Dim SatDateDay As String
Dim FriDateYear As String
Dim FriDateMonth As String
Dim FriDateDay As String
Dim ThuDateYear As String
Dim ThuDateMonth As String
Dim ThuDateDay As String
Dim WedDateYear As String
Dim WedDateMonth As String
Dim WedDateDay As String
Dim TueDateYear As String
Dim TueDateMonth As String
Dim TueDateDay As String
Dim MonDateFull As String
Dim SunDateFull As String
Dim SatDateFull As String
Dim FriDateFull As String
Dim ThuDateFull As String
Dim WedDateFull As String
Dim TueDateFull As String
Dim FilePathText As String
Dim CheckNumRows As Integer
Dim MainWorkbook As Workbook
Dim rowcount As String
MonDate = Date
SunDate = Date - 1
SatDate = Date - 2
FriDate = Date - 3
ThuDate = Date - 4
WedDate = Date - 5
TueDate = Date - 6
MonDateYear = DatePart("yyyy", MonDate)
MonDateMonth = Format(DatePart("m", MonDate), "00")
MonDateDay = Format(DatePart("d", MonDate), "00")
MonDateFull = MonDateYear & MonDateMonth & MonDateDay
SunDateYear = DatePart("yyyy", SunDate)
SunDateMonth = Format(DatePart("m", SunDate), "00")
SunDateDay = Format(DatePart("d", SunDate), "00")
SunDateFull = SunDateYear & SunDateMonth & SunDateDay
SatDateYear = DatePart("yyyy", SatDate)
SatDateMonth = Format(DatePart("m", SatDate), "00")
SatDateDay = Format(DatePart("d", SatDate), "00")
SatDateFull = SatDateYear & SatDateMonth & SatDateDay
FriDateYear = DatePart("yyyy", FriDate)
FriDateMonth = Format(DatePart("m", FriDate), "00")
FriDateDay = Format(DatePart("d", FriDate), "00")
FriDateFull = FriDateYear & FriDateMonth & FriDateDay
ThuDateYear = DatePart("yyyy", ThuDate)
ThuDateMonth = Format(DatePart("m", ThuDate), "00")
ThuDateDay = Format(DatePart("d", ThuDate), "00")
ThuDateFull = ThuDateYear & ThuDateMonth & ThuDateDay
WedDateYear = DatePart("yyyy", WedDate)
WedDateMonth = Format(DatePart("m", WedDate), "00")
WedDateDay = Format(DatePart("d", WedDate), "00")
WedDateFull = WedDateYear & WedDateMonth & WedDateDay
TueDateYear = DatePart("yyyy", TueDate)
TueDateMonth = Format(DatePart("m", TueDate), "00")
TueDateDay = Format(DatePart("d", TueDate), "00")
TueDateFull = TueDateYear & TueDateMonth & TueDateDay
FilePathText = "The beggining of the file path"
CheckNumRows = 0
With Workbooks.Open(FilePathText & MonDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
End With
With Workbooks.Open("File path for the sheet I'm pasting into")
Set MainWorkbook = ActiveWorkbook
Range("A1").Select
Selection.Paste
.Close
End With
CheckNumRows = 0
With Workbooks.Open(FilePathText & SunDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
End With
With MainWorkbook
Range("a1").End(xlDown).Offset(1, 0).Select
Selection.Paste
End With
CheckNumRows = 0
With Workbooks.Open(FilePathText & SatDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
.Close
End With
With MainWorkbook
Range("a1").End(xlDown).Offset(1, 0).Select
Selection.Paste
End With
CheckNumRows = 0
With Workbooks.Open(FilePathText & FriDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
.Close
End With
With MainWorkbook
Range("a1").End(xlDown).Offset(1, 0).Select
Selection.Paste
End With
CheckNumRows = 0
With Workbooks.Open(FilePathText & ThuDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
.Close
End With
With MainWorkbook
Range("a1").End(xlDown).Offset(1, 0).Select
Selection.Paste
End With
CheckNumRows = 0
With Workbooks.Open(FilePathText & WedDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
.Close
End With
With MainWorkbook
Range("a1").End(xlDown).Offset(1, 0).Select
Selection.Paste
End With
CheckNumRows = 0
With Workbooks.Open(FilePathText & TueDateFull & ".csv")
CheckNumRows = Cells(Row.Count, 1).End(xlUp).Row
Rows("2:" & CheckNumRows).Select
Selection.Copy
.Close
End With
With MainWorkbook
Range("a1").End(xlDown).Offset(1, 0).Select
Selection.Paste
End With
End Sub
I have tried removing all of the Dim MonDayYear As String and etc to see if the fact that it's a string rather than a date was causing it, but it doesn't seem to have helped. Unless it won't function as an undeclared variable either... All that is is to get the date into the proper format for the file extension anyway. The file extensions look like "sometext-20161031.csv" and I don't know how to get the date feature to show in YYYYMMDD.
Any help on dealing with the 424 error, the date format issue, or anything else you may see is much appreciated! Sorry this is a mess, I'm still learning.
