Replacing formulas in specific range across several worksheets (except four) with values

[Julmust Jaeger]

Hello,

I am looking to replace formulas in specific range across several worksheets (except four) with values (to speed up loading and so on).

Basically across several worksheets I generate (so hard to predetermine a list), I need to replace a bunch of formulas in A1:C310 with values (goes super slow otherwise).

I have tried the follow (and similar codes but I keep getting a memory error).

Any help would be much appreciated!

Sub ConvertFormulsToValues_Test()

    If ActiveWorkbook Is Nothing Then Exit Sub

    Dim targetAddress As String
    targetAddress = "A1:A310, B1:B310, C1:C310" 'change as desired
    
    Dim currentWorksheet As Worksheet
    For Each currentWorksheet In ActiveWorkbook.Worksheets
        If (currentWorksheet.Name <> "Company_List") And (currentWorksheet.Name <> "2019") And (currentWorksheet.Name <> "Temp") And (currentWorksheet.Name <> "Reference") Then
            With currentWorksheet.Range(targetAddress)
                .Value = .Value
            End With
        End If
    Next currentWorksheet
    
    MsgBox "Completed!", vbExclamation
    
End Sub

 

[davesexcel]

Hello,
How many sheets are there?

 

[Julmust Jaeger]

It ranges based on the particular company (a first step is I split up a big dataset of 100+ companies into several individual workbooks).

Some of these workbooks have as few as 1-2 worksheets and some have as many as 31 (lol).

Thanks!