I have a macro which selects all sheets in a workbook and then converts the formulas to values so that I can email the file in the smallest possible form.
I would like to add some pivot tables to the workbook, but the macro converts only the formulas, not the pivot tables.
How can this code be modified to include pivot tables?
'Replace all formulas in all sheets with values
Dim shtSheet As Worksheet, shtActive As Worksheet
Dim rngR As Range, rngCell As Range
Application.ScreenUpdating = False
Set shtActive = ActiveWorkbook.ActiveSheet
For Each shtSheet In ActiveWorkbook.Sheets
If .ProtectContents = False Then ' skip protected sheets
On Error Resume Next
Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _
xlErrors + xlLogical + xlNumbers + xlTextValues)
On Error GoTo 0
If Not rngR Is Nothing Then
For Each rngCell In rngR
rngCell.Value2 = rngCell.Value2
shtActive.Activate ' reset to original active sheet
Set shtActive = Nothing
Application.ScreenUpdating = True