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

MPBJR

Board Regular
Joined
Mar 28, 2007
Messages
138
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

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:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
What exactly is in cells J100 & K100?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested, but if you are changing the date every day - 7 days a week, maybe something like this:
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case try
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback
 

MPBJR

Board Regular
Joined
Mar 28, 2007
Messages
138
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
Will the other file be open when you run the code?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,542
Messages
5,529,454
Members
409,878
Latest member
DDhol
Top