Using an array to make Find and Replace quicker over 90 sheets?

[mountainman88]

I have a set of 90 identical sheets in a workbook called 'Global' , and all sheets are linked to a single tab called 'total' in another workbook called 'Financials'. The 'Financials' workbook has 90 tabs with names that correspond to the tab names in the 'global' workbook. I need to perform a Find and replace that changes the links so they are not linked to the same 'Total' tab in the 'financials' workbook, but instead link to the tab that has the corresponding name.

For example:

Currently the 'EMEA' tab in the 'Global' Workbook is linked to the 'Total' tab in the 'Financials' Workbook. I need the 'EMEA' tab in the 'Global' Workbook to be linked to the 'EMEA' tab in the 'Financials' Workbook. This needs to occur for 90 tabs, each tab has hundreds of links to the 'Total' tab that need replacing.

I have a VBA solution below using find and replace on every cell within the smallest range I can specify on each sheet - Range("AN1:FN502"), but it has to be run over night due to how many cells it is processing:

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

'the range below is a list of all the tabs that need the find and replace performed on

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.Range("AN1:FN502").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

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!

 

[Fluff]

Duplicate to: Optimize my Find and Replace VBA over multiple sheets

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.