Mass Formula Update

[rschmidt46]

One tab of a spreadsheet contains about 3,000 formulae that reference data in another tab in the workbook. The name of the referenced tab changes on a weekly basis when it is refreshed and then it reflects the new date in the form of "YYYY-MM-DD"). The referenced tab currently is named "All Volunteer MASTER 2022-08-05". The tab that refers TO this tab is named "Members by Voice Part". Is there a way to mass update the formulae with VBA in the "Members by Voice Part" tab such that every formula referencing "All Volunteer MASTER 2022-08-05" would be changed to reference "All Volunteer MASTER 2022-08-12"? Hard-coding all the formulae would be a formidable task! I have not tried anything yet, in hopes that I'll start out on the right foot with input/advice from some of you Excel VBA experts out there. A typical formula is copied below.

='All Volunteer MASTER 2022-08-05'!A2 & ", " & 'All Volunteer MASTER 2022-08-05'!B2​

 

[SpillerBD]

So you want to change the text in formula.
Have you tried the Find and Replace function (CTRL+H) with the option, "Look in: Formulas" ?

 

[rschmidt46]

So you want to change the text in formula.
Have you tried the Find and Replace function (CTRL+H) with the option, "Look in: Formulas" ?

I did not try that until just now. It did work for the two cells in which I tested it. Presumably I could record a macro and adapt it for a dynamic situation because the number of records also changes with each refresh.

 

[SpillerBD]

That particular formula is supper easy in PowerQuery.
I wonder if you would better be served using PowerBI (baked into Excel)?

 

[KenU]

For long-term simplicity, you might consider a slight change to the overall workflow for your workbook. If your formulas referred to a sheet called something like "All Volunteer Master" which contains the relevant data for the current week, you could simply make a copy of that "All Volunteer Master" sheet with the appropriate date appended for archiving. Doing so would mean that you never have to change the formulas. You could choose to keep the archived data in the same workbook or a different workbook.

I hope that helps.

Regards,
Ken

 

[Reba]

A simple option would be to put the sheet name in a cell and then reference that cell in all of the formulas, update the cell each week.

I solved a similar problem in VBA recently, but I was referencing table names and using listobjects for all of my formulas. I have a constantly changing data set so by using listobjects/columns the data range is automatically updated which was great for me. I'll put an excerpt of my code below. Ultimately, I used a lot more variables to cut down on the length of my formulas, but removed them here to make it easier to understand. If you have any questions or want more info on my methods just let me know!
If your data range doesn't change a simpler option would be to set the date or sheet name to a variable and use that in your formulas, but personally I have found the use of listobjects to be extremely helpful when changes are needed.

Sub agent_schedule_excert()
'
'

Public Saturday As String
Dim rdate As Range
Dim GS As ListObject, TB As ListObject

Sheets("Agent Schedules").Activate
Set ws = ActiveSheet
Set rdate = Sheets("Agent Schedules").Range("H2") 'Find date on schedule
Saturday = DateValue(rdate - Weekday(rdate) + 7) 'Calculate Saturday
Set TB = Sheets("Time Block").ListObject(1)

'convert data to table
ws.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
        "AS_" & Saturday
'set listobject variable
Set GS = ws.ListObjects("AS_" & Saturday)

Sheets("Time Block").Activate

 TB.ListColumns("Scheduled Hours").DataBodyRange.Formula2 = "=Xlookup([@[Date]]," _
 & GS.ListColumns("Date").DataBodyRange.Address(External:=True) & "," _
 & GS.ListColumns("Daily Hours").DataBodyRange.Address(External:=True) & ")"

End Sub