Convert pivot table worksheets to range value

Question

On the pivot table, I click add pages and it creates a new worksheet for all the items in column (A) in the pivot table format.

I need all the sheets to be converted to absolute values by using the range format values function. I have about 100 telephone routes in column (A) and it takes a long time to covert them manually.

Thanks for your support

Answer 1

There is no command in the PivotTable area to do that.  Normally you would select the pivot table and copy, then paste as values.  You could use a macro for that.

Sub ConvertPT()

    On Error Resume Next

    For Each ws In Worksheets

        If ws.Name <> "Main" Then

            ws.Activate

            ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel, True

            If Err <> 0 Then

                Err.Clear

            Else

                Selection.Copy

                [A1].Select

                Selection.PasteSpecial Paste:=xlPasteValues

            End If

        End If

    Next ws

End Sub

This macro convert all pivot table created by the Report Filter Pages command to values skipping the sheet called Main.  You would need to modify that - I'm just assuming you have the source pivot table on a sheet called Main.  You can exclude other sheet with a similar idea.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire