Dates condition in Macro

[omypet]

I have a macro that transfer data from other worksheet into a final worksheet and it’s condition is base on the filename with dates (example “myfile-2005-10-31.xls”), now the problem arises on “myfile-2005-11-01.xls” which is the Nov 1st, I have to rename the file to “myfile-2005-10-32.xls” which we don’t have Oct 32nd in order to continue picking data from the source files. This macro don't pick Nov 1. Do you have any suggestion on how to deal with dates especially for tomorrows date Nov. 1. Please !!!

Public MyLine
Sub NewDailyReports()
' by omypet
Mysheet = ActiveSheet.Name
mypath = ActiveWorkbook.Path
If Mysheet = "2005" Then
MyLine = ActiveCell.Row
FrmLine.TxtLine.Text = MyLine
FrmLine.Show
Mysheet = ActiveSheet.Name
Mydate = Range("A" & MyLine)
MycellA = "A" & MyLine
MycellB = "B" & MyLine
MycellC = "C" & MyLine
Do While Mydate < Date Or Mydate = Date And Mydate <> ""
MyDay = Day(Range("A" & MyLine)) + 1
If Len(MyDay) = 1 Then
MyDay = "0" & MyDay + 1
End If
MyMonth = Month(Range("A" & MyLine))
If Len(MyMonth) = 1 Then
MyMonth = "0" & MyMonth
End If
MyYear = Right(Year(Range("A" & MyLine)), 4)
MyDateFile1 = "myfile-" & MyYear & "-" & MyMonth & "-" & MyDay & ".xls"
If Dir(mypath & "\" & MyDateFile1) = "" Then
If Range(MycellB).Value = "" Then
Range(MycellB).Value = ""
End If
If Range(MycellC).Value = "" Then
Range(MycellC).Value = ""
End If
If Range(MycellD).Value = "" Then
Range(MycellD).Value = ""
End If
Else
FormulaB = "='" & mypath & "\[" & MyDateFile1 & "]Summary'!$K$13"
Range(MycellB).Formula = FormulaB
FormulaC = "='" & mypath & "\[" & MyDateFile1 & "]Summary'!$E$22"
Range(MycellC).Formula = FormulaC
FormulaD = "='" & mypath & "\[" & MyDateFile1 & "]Summary'!$E$23"
Range(MycellD).Formula = FormulaD
FormulaE = "='" & mypath & "\[" & MyDateFile1 & "]Production'!$M$9"
End If
MyLine = MyLine + 1
Mydate = Range("A" & MyLine)
MycellA = "A" & MyLine
MycellB = "B" & MyLine
MycellC = "C" & MyLine
MycellD = "D" & MyLine

Loop
End If
End Sub

 

[Von Pookie]

If I'm understanding you correctly:

If you have set the Mydate variable as a date, you should simply be able to use Mydate+1 to return the next day.

For example, using a simple

Date+1

will return tomorrow's date.

So if the value of Mydate is 10/27/05, using Mydate + 1 should return 10/28/05. Same thing if Mydate is 10/31/05. Mydate + 1 should return 11/1/05.

 

[omypet]

I started using this macro only on the 17th of this month Oct and seems to be working until 31st but not on Nov 1 anymore, I’ve tried mydate + 1 after the loop to get the next month result but to no avail.

 

[Von Pookie]

Not fully tested, but I believe it should get the dates correctly:

Public MyLine
Sub NewDailyReports()
Dim MySheet As String, MyPath As String
Dim MyDate As Date, MyDay As Integer
Dim MyYear As Integer, MyMonth As Integer
Dim MyDateFile1 As String
Dim MyCellA As Range, MyCellB As Range
Dim MyCellC As Range, MyCellD As Range
Dim FormulaB As String, FormulaC As String
Dim FormulaD As String, FormulaE As String

MySheet = ActiveSheet.Name
MyPath = ActiveWorkbook.Path

If MySheet = "2005" Then
    MyLine = ActiveCell.Row
    'FrmLine.TxtLine.Text = MyLine
    'FrmLine.Show
    MyDate = Cells(MyLine, "A").Value

    Do While IsDate(MyDate) = True And _
    MyDate < Date Or MyDate = Date
        Set MyCellA = Cells(MyLine, "A")
        Set MyCellB = MyCellA.Offset(, 1)
        Set MyCellC = MyCellB.Offset(, 1)
        Set MyCellD = MyCellC.Offset(, 1)
        
        MyDateFile1 = "myfile-" & Format(MyDate, "yyyy-mm-dd") & ".xls"
        MsgBox MyDateFile1
        
        If Dir(MyPath & "\" & MyDateFile1) = "" Then
            If MyCellB.Value = "" Then MyCellB.Value = ""
            If MyCellC.Value = "" Then MyCellC.Value = ""
            If MyCellD.Value = "" Then MyCellD.Value = ""
        Else
            FormulaB = "='" & MyPath & "\[" & MyDateFile1 & "]Summary'!$K$13"
            MyCellB.Formula = FormulaB
            
            FormulaC = "='" & MyPath & "\[" & MyDateFile1 & "]Summary'!$E$22"
            MyCellC.Formula = FormulaC
            
            FormulaD = "='" & MyPath & "\[" & MyDateFile1 & "]Summary'!$E$23"
            MyCellD.Formula = FormulaD
            
            FormulaE = "='" & MyPath & "\[" & MyDateFile1 & "]Production'!$M$9"
        End If
        
        MyLine = MyLine + 1
        MyDate = Cells(MyLine, "A").Value
    
    Loop
End If

End Sub

 

[omypet]

Tks a lot von pookie for this idea you've shared, setting dim looks will works with this one. Appreciated your help