Convert Pivot Table to Values in Macro

B

Buck

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top