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
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
Code:
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
Last edited: