Using an array to store formulas, replace part of the string and then insert to identical sheet

[mountainman88]

I was using the below code to copy the usedrange formulas from a sheet called 'template' store them in an array, replace part of the formula string within the array, copy to another array, and then insert that array's contents to an identical new tab, multiple times based on a list of tabs.

The replacearray is working and holds the formulas I need with the replace text, but inserting to the new tab doesn't occur.

This was working in a previous iteration of my work but now it errors on this line:

ws3.UsedRange.Formula = ReplaceArray

I really don't understand as the usedrange is identical on both sheets (because one is a copy of the other) and I have proved this. Im sure this worked before now I don't know what changed!

Sub Step4xArrayreplace()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, c As Range, l As Range, r As Range, TempArray() As Variant, ReplaceArray() As Variant

'Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False

Set ws2 = Sheets("template")
Set ws1 = Sheets("list")

ws1.Activate

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

'populate array with template formulas

TempArray = ws2.UsedRange.Formula

'start cycling through the replace tabs to insert the formulas based on the list of tabs

For Each l In ws1.Range("a1", "a" & RowCount)

        Set ws3 = Sheets(l.Value)
       
'copy the template formulas from template array to replace array
       
ReplaceArray = TempArray

'replace the formulas in the replace array with the tab name

For i = 1 To UBound(ReplaceArray)
    For j = 1 To UBound(ReplaceArray, 2)

    ReplaceArray(i, j) = Replace(ReplaceArray(i, j), "TOTAL'!", l.Value & "'!")
     Next
Next

'copy from the replace array to the replace sheet

        ws3.UsedRange.Formula = ReplaceArray
     
Next l

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Many thanks in advance for your help!

 

[RoryA]

What's the error?