The code below converts multiple worksheets to values. Except, as I
discovered recently Pivot Tables. Can you help modify the code to also
convert Pivot Tables to Values?
=============
Sub SetAllSheetsToValues()
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
With shtSheet
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
Next rngCell
End If
End If
End With
Next shtSheet
shtActive.Activate ' reset to original active sheet
[a2].Activate
Set shtActive = Nothing
Application.ScreenUpdating = True
End Sub
=============
I modified this line (below) to include "+xlPivotTables" and ran it. It
didn't bomb out but neither did it convert to values.
Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _
xlErrors + xlLogical + xlNumbers + xlTextValues)
|
Similar Threads
