title: Excel - Converting certain formulae to values - performance issues
body: <p>I am working with a series of large workbooks and using a tool to import values from a financial database (SAP Financial Consolidation). The way it works is by using UDFs such as <code>=GetCtData({parameters})</code> or <code>=GetCtLabel({parameters})</code>. One of the parameters is the output value of the cell, such that at any point the value of the cell is a number. </p>
  
  <p>To share these workbooks with others who do not have the Financial Consolidation add-in I need to convert each of these cells to values. I don't want to convert all cells to values, only the cells with the <code>=GetCt...</code> formulae. Below is the code I have written so far, which has three (similar) approaches (two are commented out). It works perfectly on small workbooks but the files have now grown such that there are perhaps a total of 250,000+ cells which need updating. (Roughly 70 columns x 350 rows x 10+ worksheets.) I've tried running it but after a couple of hours it is still going.</p>
  
  <p>Can anyone suggest a more efficient method?</p>
  
  <pre><code>Sub removeAllMagnitudeLinks()
  
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Application.EnableEvents = False
  
      For Each sSheet In Worksheets
          If sSheet.Name &lt;&gt; "blankMagnitude" Then 'Don't remove links from the blankMagnitude sheet -- unnecessary
              If sSheet.FilterMode Then sSheet.ShowAllData
  
              Application.StatusBar = "Working on sheet #" &amp; sSheet.Index &amp; " of " &amp; Worksheets.Count &amp; ". Name: " &amp; sSheet.Name
  
              On Error Resume Next
              While Err.Number = 0
                  With sSheet.Cells.Find(What:="GetCt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  '                    .Copy 'Copying and pasting is one approach, but may not be fastest
  '                    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  '                    .Formula = .Value 'This is another approach, which is certainly not very fast
                      .Value = .Value
                  End With
              Wend
          End If
      Next sSheet
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      Application.EnableEvents = True
  End Sub
  </code></pre>