Create tabs from pivot table

Question

Hi,

I have pivot table, i am creating different tabs from the pivot by double clicking each row and renaming them tabs with another name.

For example If the Sheet name is "Raw" where the pivot table is there. Row A5 to A25 has the data for which i have to double click each row and rename on the basis of the text from A5 to A25 to create 25 sheets

Is there any tool by which i can split the rows from the pivot into different sheet. And name the sheets as per specific cell values (A5 to A25 here as example).

I don't want report filter pages where it will create different pivots for each category. I need the source data that represent for each row in the pivot.

Regards,

Radhakant

Answer 1

Sorry - I read your post as you clicking in A - change the Address to the cells that you click on - for example, in column B

Sub MacroToShowDetails()

    Dim r As Range

    For Each r In Range("B5:B25")

        r.ShowDetail = True

        ActiveSheet.Name = r.Offset(0,1-r.Column).Value

    Next r

End Sub

Answer 2

You can rerun it after refreshing the pivot by deleting the sheet(s) first in code:

Sub MacroToShowDetails2()

    Dim r As Range

    'Delete the existing sheets first

    Application.DisplayAlerts = False

    On Error Resume Next

    For Each r In Range("B5:B25")

        Worksheets(r.Offset(0, 1 - r.Column).Value).Delete

    Next r

    Application.DisplayAlerts = True

    On Error GoTo 0

    For Each r In Range("B5:B25")

        r.ShowDetail = True

        ActiveSheet.Name = r.Offset(0, 1 - r.Column).Value

    Next r

End Sub

Answer 3

Thanks a lot Bernie....it is working perfectly. :)

Can this work when i refresh pivot and run the macro. Or i have to 1st delete all the tabs and then run the macro.

Answer 4

Thanks Bernie for answering, but it is not working.

For example below is my pivot table, and when i double click on the each amount separately it opens up in a new tab as "Sheet4", "Sheet5" etc. Then i name them as "Jan12", "Feb 12"etc.

And if i move the months to filters in the pivot and go to Options > Show Report Filter Pages. It creates separate sheets with the name of the month, but the data comes in pivot table. But i need the data to come like when i double click in the amount in the original table.

Row Labels	Sum of Amount
Jan12	(6,828,924.24)
Feb12	(268,961.15)
Mar13	(3,227,933.40)
Apr14	(204,714.59)
May12	(2,431,485.97)
Grand Total	(12,962,019.35)

Answer 5

With the pivot table sheet "Raw" active, try:

Sub MacroToShowDetails()

    Dim r As Range

    For Each r In Range("A5:A25")

        r.ShowDetail = True

        ActiveSheet.Name = r.Value

    Next r

End Sub