Guys,

More help please...

The function of this sub routine is to run through all the sheets in the workbook and copy and paste special the formulas to values ->except formulas that contain the "subtotal" or "sum" function.

The paste special prt works, but I am unable to make it conditional so that it skips over the subtotal and sum finctions. I have been searching the forum for hours trying to find a similar thread and tried all different codes of my own, but I am stumped.

I would like help with the following:
  • Insert "if" statement to skip formulas containing "subtototal" and "sum" functions
  • Simplify the code and prevent screen from "flashing" as it executes
  • Show a progress bar with a message to be patient as it takes some time to complete?



Here is my code so far..
Code:
Sub FormulasToValues()


Dim dataSheet As Worksheet
Dim lstRow As Integer
Dim Row As Integer

frmExport.Show
For Each dataSheet In ActiveWorkbook.Sheets
     lstRow = dataSheet.UsedRange.Row + dataSheet.UsedRange.Rows.Count - 1
        For Row = lstRow To 1 Step -1
            'selects the entire row and then search for rows containing subtotals
            With dataSheet.Rows(Row).EntireRow
            
' here is where I need a if statement that is something to the extend of:
' "if any cell in the row contains a formula with the subtotal function
' in it, then skip the following steps else continue"
                
                .Copy
                'Paste the copied cells as values instead of formulas
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            End With
frmExport.Hide
        Next Row
Next dataSheet

End Sub
Help will be appreciated.