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

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

0 Replies
1550 Views

Similar Articles

[PageSpeed] 5

Reply:

Similar Artilces: