Optimize my Find and Replace VBA over multiple sheets

[mountainman88]

I have 90 identically structured tabs in a workbook each with their own individual tab name, they link to corresponding tabs on other workbooks eg. tab NA in this workbook links to tab NA in the other workbooks, tab EMEA in this workbook links to tab EMEA in the other workbooks etc

When I need to make changes to the structure of the sheets it is impossible to group select all 90 tabs and make the changes. So instead, I edit one tab alone in a new workbook called TOTAL and then use VBA to duplicate it 90 times and rename the 90 tabs. I have all the tabs I need, labeled individually, but the issue is they all link to the TOTAL tabs on the other workbooks instead of their corresponding tab.

Each sheet is a P&L with hundreds of links, I have used the below VBA to take the tab names from a sheet called 'list', and then in each tab perform a Find and Replace on the links by replacing "TOTAL'!" with the tabname, eg. the VBA will get to EMEA in the list, then perform the Find and Replace on the EMEA tab and replace any instance of "TOTAL'!" with "EMEA'!".

Is there any way to make this process quicker? Can the code be optimized more?

Sub FindReplaceAll()

Dim ws1 As Worksheet, ws2 As Worksheet, c As Range
Dim fnd As Variant
Dim rplc As Variant

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

fnd = "TOTAL'!"

Set ws1 = Sheets("list")
RowCount = ws1.Range("a1", Range("a2").End(xlDown)).Count


For Each c In ws1.Range("a1", "a" & RowCount)
    
    
'Store a specific sheet to a variable
  Set ws2 = Sheets(c.Value)
 
  rplc = ws2.Name & "'!"

'Perform the Find/Replace All
  ws2.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
 Next c
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Thanks

 

[mountainman88]

Another approach im trying is to use replace but nothing is happening:

                    ft = c.Formula
                    ft = Replace(ft, "Total", "EMEA")
                    c.Formula = ft

replace function doesn't seem to register the formula as text to replace?

 

[mountainman88]

I've noticed that straight up inserting a formula into a cell is much quicker than finding it, evaluating it, replacing text and inserting it.

So I was wondering is there a way to store all of the formulas with the Total'! text that needs to be replaced in an array, and then for each tab name, performing the replace within the array, and then just overwriting the formulas on the tabs that require the replacing?

Either this or making the above code take less than 15 hours to run!

Many Thanks!