Mass Find/Replace other worksheet references

[lost_in_the_sauce]

Trying to update about 200 cells that reference another workbook - looks like this:

='F:\AOP\Monthly P&L\FY21\P8 - Feb 21\[FY21 P8 - Presentation.xlsx]Sales'!$DR$8

And I want to change every cell to this:

='F:\AOP\Monthly P&L\FY21\P9 - Mar 21\[FY21 P9 - Presentation.xlsx]Sales'!$DR$8

When I do they, for every cell it creates a pop-up to point at the new workbook. Is there a way around that since normally it changes everything and lets me know how many and the file reference change wants more?

 

[fadee2]

In vba try the following code.....

Sub lost()
Application.DisplayAlerts = False
For x = 1 To 34
    Cells(x, 1).Select
    Sheets("Sheet1").Cells(x, 1).Replace What:="='F:\AOP\Monthly P&L\FY21\P8 - Feb 21\[FY21 P8 - Presentation.xlsx]Sales'!$DR$8", _
                    Replacement:="='F:\AOP\Monthly P&L\FY21\P9 - Mar 21\[FY21 P9 - Presentation.xlsx]Sales'!$DR$8"
Next x

Application.DisplayAlerts = True
End Sub

this will change your text in each cell if it ='F:\AOP\Monthly P&L\FY21\P8 - Feb 21\[FY21 P8 - Presentation.xlsx]Sales'!$DR$8 to "='F:\AOP\Monthly P&L\FY21\P9 - Mar 21\[FY21 P9 - Presentation.xlsx]Sales'!$DR$8", while displayalerts = false statements hides all alerts...


hope this helps.