Find/Replace? to change a sheet name in a formula

[MPBJR]

I have a range on a sheet (Q89:S100) that I have formulas in to pull data from a file on a server. Every day this file gets updated and a new sheet is added with that days data on it, the sheet has a name format of 031819. I need to have a macro that runs at 5pm everyday that changes the sheet name in the formula to the newest sheet in the source file to get the most updated data.

For example, the formula I have in Q89 is:
='\\server\folder1\folder2\[source file.xlsx]031319'!B2

So the next day, i need to update all the formulas to pull data from 031419 (B2 will remain the same).

I've tried various macros for find & replace but can't get them to work.

I've set up 2 cells as a find value and replace value but can't get it to work.

I know how to set up the macro to run at a certain time, just can't figure out this find and replace part.

Any help would be greatly appreciated. Thanks

Sub UPDATE()
'
' UPDATE Macro
'

    
    
    
    Dim fnd As String
    Dim rplc As String

    fnd = Cells(100, "J").Value
    rplc = Cells(100, "K").Value
    Range("Q89:S100").Select
    Selection.Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[Fluff]

What exactly is in cells J100 & K100?

 

[JoeMo]

Untested, but if you are changing the date every day - 7 days a week, maybe something like this:

Sub ChangeDate()
Dim lastDate As String, nextDate As String
lastDate = CStr(Format(Month(Date - 1), "00") & Format(Day(Date - 1), "00") & Format(Right(Year(Date - 1), 2), "00"))
nextDate = CStr(Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Right(Year(Date), 2), "00"))
Range("Q89").Formula = Replace(Range("Q89").Formula, lastDate, nextDate)
End Sub

 

[MPBJR]

The find date and the replace date

 

[Fluff]

In that case try

Sub UPDATE()
'
' UPDATE Macro
    Dim fnd As String
    Dim rplc As String

    fnd = Format(Cells(100, "J").Value, "mmddyy")
    rplc = Format(Cells(100, "K").Value, "mmddyy")
    Range("Q89:S100").Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[MPBJR]

That worked! thanks!

 

[Fluff]

You're welcome & thanks for the feedback

 

[MPBJR]

Just wanted to see if anyone could help with an add on to the above code. I now want to check that the sheet name exists in the external file that I'm pulling the data from before I run the find/replace code.
The name of the sheet would be the same value as the "rplc = Format(Cells(100, "K").Value, "mmddyy")" line in the above code. If the sheet name in k100 exists in the external doc, then I want to run the find replace code provided by Fluff, if it does not exist, I simply want the code to stop running. Thanks

 

[Fluff]

Will the other file be open when you run the code?

 

[MPBJR]

No, it will be closed.