Convert Pivot Table to Values in Macro

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
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)

3/16/2010 9:54:01 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 5


Similar Artilces: