# VBA code for showing detail from pivot tables into different sheets



## hetal247

Hi,

Can anyone help me with a query as i'm not too good with vba codes but can record macros but not for what i need.

I have got a pivot table which has about 200 rows. In Column A is the provider and in column B is the total number of people.

I now need to create a sheet per provider and the manual way to do is to double-click on the numbers.

Is there a code that would this automatically?

I appreciate all the help you can give

regards

Hetal


----------



## Jerry Sullivan

Hi Hetal and Welcome to the Board,

You could try this VBA code. Copy it into a Standard Code Module.
It assumes you have only one PivotTable on the ActiveSheet.



		Code:
__


Sub MakeReports()
    Dim c As Range
    With ActiveSheet.PivotTables(1)
        For Each c In .DataBodyRange.Resize(, 1)
           c.ShowDetail = True
        Next c
    End With
End Sub


----------



## hetal247

Hi Jerry,

Thank you for this. It works perfectly. I was also wondering if the sheet could be named as one of the cell references? For example, when the detail is created on a new sheet and i wanted it named as what is shown in cell C2, can this be done? I can do this by adding a line to the code you have provided

Sub MakeReports()
    Dim c As Range
    With ActiveSheet.PivotTables(1)
        For Each c In .DataBodyRange.Resize(, 1)
           c.ShowDetail = True
           ActiveSheet.Name = [L2]
        Next c
    End With
End Sub


However, the Cell Reference could change so i need something that says:

Find the column heading PROVIDER and rename sheet as the row below. E.g PROVIDER heading in on L1 so rename sheet as L2

Can this be done?

regards

Hetal


----------



## Jerry Sullivan

Hetal, Is your report format Outline, Tabular, or Compact?


----------



## hetal247

Hi Jerry,

My pivot table is in tabular format

Hetal


----------



## Jerry Sullivan

You can try this...


		Code:
__


Sub MakeReports2()
    Dim lCol As Long
    Dim c As Range
    Dim sField As String
    sField = "PROVIDER"
    
    With ActiveSheet.PivotTables(1)
        With .RowRange
            On Error Resume Next
            lCol = WorksheetFunction.Match(sField, .Resize(1), 0)
            On Error GoTo 0
            If lCol = 0 Then
                MsgBox "Rowfield Header: " & sField & "not found."
                Exit Sub
            End If
            lCol = .Column + lCol - 1
        End With
        For Each c In .DataBodyRange.Resize(, 1)
           c.ShowDetail = True
           ActiveSheet.Name = .Parent.Cells(c.Row, lCol)
        Next c
    End With
End Sub


----------



## hetal247

Jerry, you are a star. Thank you very much. Works as i want it to work

Appreciate all the help you have given me

Hetal


----------



## Jerry Sullivan

I'm happy to have helped you. Thanks for the kind feedback!


----------



## PSareen

Jerry Sullivan said:


> I'm happy to have helped you. Thanks for the kind feedback!



Hi Jerry,

I have a similiar requirement but involves sharepoint. Please let me know if you can help. My query is as follows

I am a sharepoint 2013 user with full permisisons. I am creating a page and i want a pivot table to be displayed on the page. 
For this i have kept one xls containing the data and a pivot table ( created in the excel sheet itself) in the shared documents. I named ithe pivot table as MyPivotTable.
Now i created a page. Added a web part. Selected Business Data> Excel Web access. Selected the workbook present in my shared documents and in the 'named item' i gave 'MyPivotTable' so that the pivot table in my excel sheet gets displayed. Now in the displayed pivot table i can expand collapse the items. But what i need is that the users for whom i am creating this web page..they should be able to drill down and show details capability is available in the web page and the users can have a look at the data ..like we are able to do it in the excel sheet. Like you explained in your post out here. that feature of pivot table is lost when its diosplayed as a web page in sharepoint.

for example if i have grouped on a column A and i am displaying the count of column B based on the grouping done in my pivot table ., Then in this this pivot table ( when seen in xls) if i click on the count it creates a seperate sheet and displays me the particular rows associated with that count.  This drill down/ show details feature is lost when i display the pivot table as descrived above.

need to get thsi done ..but so far not able to come up witha solution for this. 

Regards,
Puneet


----------



## Jerry Sullivan

Hi Puneet,

Unfortunately, I haven't yet worked with SharePoint 2013 with Business Connectivity Services so I'm not able to help you with this. 
You'll probably have better luck starting a new thread as threads with zero replies tend to get more notice than old threads.

Good luck- I'm interested to see if there's a way to do what you describe.


----------



## hetal247

Hi,

Can anyone help me with a query as i'm not too good with vba codes but can record macros but not for what i need.

I have got a pivot table which has about 200 rows. In Column A is the provider and in column B is the total number of people.

I now need to create a sheet per provider and the manual way to do is to double-click on the numbers.

Is there a code that would this automatically?

I appreciate all the help you can give

regards

Hetal


----------



## PSareen

Thanks Jerry.

I have created a new post to get my query answered:

http://www.mrexcel.com/forum/excel-...t-capability-showing-details.html#post3757871

Lets see if somebody has a solution to my query.

Regards,
Puneet


----------



## uzoyabuzo

Hi, I've tried this piece of code but the result is divided in several sheets.



		Code:
__


    Sub MakeReports()     
       Dim c As Range     
       With ActiveSheet.PivotTables(1)         
          For Each c In .DataBodyRange.Resize(, 1)            
             c.ShowDetail = True         
          Next c     
       End With 
    End Sub



Is it possible to have it in one single sheet ? Like when you double-clic on the 'total' field ?

Thanks for your help !
Víctor


----------



## willstandbyyou

Hi Jerry, I have a similar situation and applied the solution above and it works. But, I don't want "Show Details" to be done for "Grand Total". How do I exclude that? The code I used is:
------------------------------------------------------------
    With ActiveSheet.PivotTables("Elvira_Clearing")
       For Each c In .DataBodyRange.Resize(, 3)
            c.ShowDetail = True
        Next c
    End With

    Worksheets(1).Activate
------------------------------------------------------------


----------



## Jerry Sullivan

Hi willstandbyyou,

This should work...


		Code:
__


 With ActiveSheet.PivotTables("Elvira_Clearing")
   For Each c In .DataBodyRange.Resize( _
      .DataBodyRange.Rows.Count + .ColumnGrand, 3)
      c.ShowDetail = True
   Next c
 End With


----------



## willstandbyyou

Thank you so much Jerry... This worked


----------



## VickyW

* This is a long shot given the original post was 2011! So fingers crossed *

Hi Jerry

I used your script recently to brilliant effect.
Everything was ticking along fine until a new requirement came up which meant adding 2nd pivot table to each sheet
(one directly below the other - as the tables only expand horizontally as months are added/removed)

Now when the script it loops through the sheets it shows the details for pivottable(2) instead of pivottable(1)

Is there any way to modify the script to get around this?



		VBA Code:
__


'Open pivot details on a new sheet
Dim rLastCell As Range
With ActiveSheet.PivotTables(1).TableRange1
    Set rLastCell = .Cells(.Rows.Count, .Columns.Count)
End With

rLastCell.Select

Selection.ShowDetail = True

End If


All the best
Vicky


----------



## KavitaSawaratkar

Hi Jerry,

I wanted to build the same macro coding which Hetal had to but when i run the macro , c.ShowDetails = True does not work and does not open any new sheets


----------



## Xxlozzelxx

Jerry Sullivan said:


> You can try this...
> 
> 
> Code:
> __
> 
> 
> Sub MakeReports2()
> Dim lCol As Long
> Dim c As Range
> Dim sField As String
> sField = "PROVIDER"
> 
> With ActiveSheet.PivotTables(1)
> With .RowRange
> On Error Resume Next
> lCol = WorksheetFunction.Match(sField, .Resize(1), 0)
> On Error GoTo 0
> If lCol = 0 Then
> MsgBox "Rowfield Header: " & sField & "not found."
> Exit Sub
> End If
> lCol = .Column + lCol - 1
> End With
> For Each c In .DataBodyRange.Resize(, 1)
> c.ShowDetail = True
> ActiveSheet.Name = .Parent.Cells(c.Row, lCol)
> Next c
> End With
> End Sub


----------



## Xxlozzelxx

Jerry Sullivan said:


> You can try this...
> 
> 
> Code:
> __
> 
> 
> Sub MakeReports2()
> Dim lCol As Long
> Dim c As Range
> Dim sField As String
> sField = "PROVIDER"
> 
> With ActiveSheet.PivotTables(1)
> With .RowRange
> On Error Resume Next
> lCol = WorksheetFunction.Match(sField, .Resize(1), 0)
> On Error GoTo 0
> If lCol = 0 Then
> MsgBox "Rowfield Header: " & sField & "not found."
> Exit Sub
> End If
> lCol = .Column + lCol - 1
> End With
> For Each c In .DataBodyRange.Resize(, 1)
> c.ShowDetail = True
> ActiveSheet.Name = .Parent.Cells(c.Row, lCol)
> Next c
> End With
> End Sub


Thank you so much for this! Exactly what I needed


----------



## KHYAMI

Hi Jerry,

I am following this thread and tried the first code to show pivot table details in separate sheets and worked fine. I am trying to name each sheet based on the cell in the first column. The Pivot is in tabular mode and consists of three columns. the name of each sheet should be based on the cell value in the first column. 

Your kind assistance on this is much appreciated.


----------

