ahuang3433
New Member
- Joined
- Jan 24, 2017
- Messages
- 39
I have this code where I hyperlink based on the today's date. Is there a way to make it look for the latest date just incase the a new file was not ran for the day?
VBA Code:
Dim lastRow As Long
Dim myPath As String, fileName As String
myPath = "Z:\Other\PjM_Documents\ESR Material Planning\Material Readiness\"
lastRow = Range("A" & Rows.count).End(xlUp).Row
For i = 2 To lastRow
fileName = myPath & Range("A" & i).Value & " " & Format(Date, "MM-DD-YY") & "*.xlsm"
If Len(Dir(fileName)) <> 0 Then
ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
End If