Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Convert Pivot table back to Data Table | Excel Discussion (Misc queries) | |||
convert Pivot table back to Data table | Excel Discussion (Misc queries) | |||
Selecting a range of values on pivot table attribute with a macro | Excel Discussion (Misc queries) | |||
Convert Pivot Table to Normal Data table | Excel Discussion (Misc queries) |