SOLVED

Way to automatically expand all pivot tables in a notebook?

%3CLINGO-SUB%20id%3D%22lingo-sub-2066314%22%20slang%3D%22en-US%22%3EWay%20to%20automatically%20expand%20all%20pivot%20tables%20in%20a%20notebook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066314%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Excel%20in%20Office%20365%20for%20Windows%20(lastest%20version).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20my%20systems%20generates%20an%20Excel%20notebook%20with%20a%20few%20hundred%20tabs%2C%20about%2050%20of%20which%20contain%20pivot%20tables.%20%26nbsp%3BAll%20the%20pivot%20tables%20are%20collapsed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20save%20that%20notebook%20without%20manually%20expanding%20each%20pivot%20table%20in%20each%20tab%2C%20all%20the%20data%20hidden%20within%20them%20is%20lost.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20automatically%20expand%20all%20the%20pivot%20tables%2C%20in%20all%20tabs%2C%20in%20a%20notebook%3F%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20this%20be%20done%20within%20Excel%20itself%2C%20or%20does%20it%20require%20a%20macro%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20find%20this%2C%20from%202014%2C%20which%20claims%20to%20*collapse*%20all%20pivot%20tables%20in%20a%20notebook%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Fcollapse-all-pivot-tables-in-workbook.828962%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Fcollapse-all-pivot-tables-in-workbook.828962%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2066314%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067441%22%20slang%3D%22de-DE%22%3ESubject%3A%20Way%20to%20automatically%20expand%20all%20pivot%20tables%20in%20a%20notebook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067441%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931228%22%20target%3D%22_blank%22%3E%40byte99%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20another%20example%20-%20unfortunately%20in%20Deutach%20(you%20have%20to%20translate%20it%20where%20you%20need%20it)%2C%20maybe%20it%20will%20help%20your%20projects.%3CBR%20%2F%3EMaybe%20that%20will%20bring%20you%20a%20little%20closer%20to%20your%20goal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.luis.uni-hannover.de%2Ffileadmin%2Fkurse%2Fmaterial%2FVBA%2FExcel2007%2Fexcel_PivotTable.pdf%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.luis.uni-hannover.de%2Ffileadmin%2Fkurse%2Fmaterial%2FVBA%2FExcel2007%2Fexcel_PivotTable.pdf%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20could%20help%20you%20at%20least%20a%20little.%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20you%20continued%20success%20with%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067880%22%20slang%3D%22en-US%22%3EBetreff%3A%20Way%20to%20automatically%20expand%20all%20pivot%20tables%20in%20a%20notebook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20trying%2C%20but%20I'm%20afraid%20that%20I%20don't%20speak%20German%2C%20and%20a%20Google%20translation%20isn't%20typically%20sufficient%20for%20technical%20instructions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067884%22%20slang%3D%22en-US%22%3ERe%3A%20Way%20to%20automatically%20expand%20all%20pivot%20tables%20in%20a%20notebook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067884%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931228%22%20target%3D%22_blank%22%3E%40byte99%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20wanted%20to%20check%20what%20you%20mean%20by%20this%20%3CEM%3E%22If%20I%20save%20that%20notebook%20without%20manually%20expanding%20each%20pivot%20table%20in%20each%20tab%2C%20all%20the%20data%20hidden%20within%20them%20is%20lost.%20%22%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESounds%20unusual%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067969%22%20slang%3D%22en-US%22%3ERe%3A%20Way%20to%20automatically%20expand%20all%20pivot%20tables%20in%20a%20notebook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20expand%20the%20pivot%20tables%20so%20that%20I%20can%20see%20the%20underlying%20data%2C%20and%20then%20save%20it%2C%20all%20the%20underlying%20data%20is%20saved%20within%20the%20workbook.%20%26nbsp%3BBut%20if%20I%20leave%20the%20pivot%20table%20as-is%20(collapsed)%2C%20save%20the%20workbook%2C%20and%20then%20try%20to%20expand%20the%20pivot%20table%2C%20I%20get%20this%20error%20message%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22byte99_0-1610853454338.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247012iBC281F63293724CD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22byte99_0-1610853454338.png%22%20alt%3D%22byte99_0-1610853454338.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20if%20use%20the%20Refresh%20Data%20command%2C%20I%20get%20this%20message%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22byte99_1-1610853564070.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247013i279477799DFB5BBB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22byte99_1-1610853564070.png%22%20alt%3D%22byte99_1-1610853564070.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20I%20then%20click%20OK%2C%20it%20tells%20me%20it%20can't%20link%20to%20the%20source%20file.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20a%20known%20issue%20where%20I%20work.%20Our%20IT%20dept.%20is%20aware%20of%20it.%20I%20suspect%20the%20problem%20is%20that%2C%20when%20the%20files%20are%20generated%2C%20they%20are%20generated%20with%20links%20to%20the%20underlying%20data%2C%20rather%20than%20the%20underlying%20data%20itself.%20%26nbsp%3BWhen%20you%20open%20the%20pivot%20table%2C%20it%20imports%20that%20data.%20%26nbsp%3B%20But%20if%20you%20save%20before%20doing%20this%2C%20the%20link%20is%20broken%2C%20and%20it%20is%20no%20longer%20possible%20to%20import%20the%20data.%20%26nbsp%3BThus%20our%20SOP%20is%20to%20first%20manually%20expand%20all%20the%20pivot%20tables%2C%20and%20then%20save.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20might%20be%20related%20to%20this%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.pivot-table.com%2F2015%2F01%2F28%2Fsaving-source-data-with-pivot-table-file%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.pivot-table.com%2F2015%2F01%2F28%2Fsaving-source-data-with-pivot-table-file%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20understably%2C%20I%20don't%20want%20to%20go%20into%20each%20of%20the%2050%20tabs%20and%20change%20a%20setting%20for%20each%20pivot%20table.%20%26nbsp%3BAnd%20even%20if%20there%20were%20one%20setting%20I%20could%20change%20for%20the%20entire%20workbook%2C%20to%20ensure%20all%20data%20is%20saved%2C%20I%20would%20still%20need%20to%20expand%20all%20the%20pivot%20tables%20manually%20anyways%2C%20for%20presentation%20purposes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2068091%22%20slang%3D%22en-US%22%3ERe%3A%20Way%20to%20automatically%20expand%20all%20pivot%20tables%20in%20a%20notebook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2068091%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931228%22%20target%3D%22_blank%22%3E%40byte99%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAh%20ok%2C%20sounds%20like%20it%20will%20be%20a%20macro...%20there's%20another%20one%20here%20which%20you%20could%20combine%20with%20a%20for%20each%20Pivot%20routine...%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Fvba%2Fexpand-collapse-entire-pivot-table-fields-buttons%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelcampus.com%2Fvba%2Fexpand-collapse-entire-pivot-table-fields-buttons%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20For%20Each%20sh%20In%20ThisWorkbook.Worksheets%0A%0A%20%20%20%20%20%20%20%20'If%20there%20is%20any%20pivot%20table%20on%20the%20sheet%0A%20%20%20%20%20%20%20%20If%20sh.PivotTables.Count%20%26gt%3B%200%20Then%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20'Loop%20through%20all%20the%20pivots%20on%20the%20sheet%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20Each%20pvt%20In%20sh.PivotTables%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Call%20ExpandPivotFields%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20pvt%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20End%20If%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESounds%20like%20the%20%22ThisWorkbook%22%20element%20will%20need%20to%20be%20changed%20so%20you%20can%20save%20the%20routine%20in%20a%20separate%20workbook%20to%20run%20each%20time.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 

I'm using Excel in Office 365 for Windows (lastest version).

 

One of my systems generates an Excel notebook with a few hundred tabs, about 50 of which contain pivot tables.  All the pivot tables are collapsed.

 

If I save that notebook without manually expanding each pivot table in each tab, all the data hidden within them is lost. 

 

Is there a way to automatically expand all the pivot tables, in all tabs, in a notebook?  

Can this be done within Excel itself, or does it require a macro?

 

I did find this, from 2014, which claims to *collapse* all pivot tables in a notebook:
https://www.mrexcel.com/board/threads/collapse-all-pivot-tables-in-workbook.828962/

10 Replies

@byte99 

 

Here is another example - unfortunately in Deutach (you have to translate it where you need it), maybe it will help your projects.
Maybe that will bring you a little closer to your goal.

 

https://www.luis.uni-hannover.de/fileadmin/kurse/material/VBA/Excel2007/excel_PivotTable.pdf

 

 

Hope I could help you at least a little.

I would be happy to know if I could help.

 

I wish you continued success with Excel

 

Nikolino

I know I don't know anything (Socrates)

@Nikolino  Thanks for trying, but I'm afraid that I don't speak German, and a Google translation isn't typically sufficient for technical instructions.

Hi @byte99 

 

Just wanted to check what you mean by this "If I save that notebook without manually expanding each pivot table in each tab, all the data hidden within them is lost. "

 

Sounds unusual

@Wyn Hopkins 

 

If I expand the pivot tables so that I can see the underlying data, and then save the workbook, all the underlying data is saved within the workbook.  But if I leave the pivot table as-is (collapsed), save the workbook, and then try to expand the pivot table, I get this error message:

byte99_0-1610853454338.png

 

If I use the Refresh Data command, I get this message:

byte99_1-1610853564070.png

 

But if I then click OK, it tells me it can't link to the source file.

This is a known issue where I work. Our IT dept. is aware of it. I suspect the problem is that, when the files are generated, they are generated with links to the underlying data, rather than the underlying data itself.  When you open the pivot table, it imports that data.   But if you save before doing this, the link is broken, and it is no longer possible to import the data.  Thus our SOP is to first manually expand all the pivot tables, and then save.

 

It might be related to this:
https://www.pivot-table.com/2015/01/28/saving-source-data-with-pivot-table-file/

But, understably, I don't want to go into each of the 50 tabs and change a setting for each pivot table.  And even if there were one setting I could change for the entire workbook, to ensure all data is saved, I would still need to expand all the pivot tables manually anyways, for presentation purposes.

Hi @byte99 

 

Ah ok, sounds like it will be a macro... there's another one here which you could combine with a for each Pivot routine...

https://www.excelcampus.com/vba/expand-collapse-entire-pivot-table-fields-buttons/

 

 For Each sh In ThisWorkbook.Worksheets

        'If there is any pivot table on the sheet
        If sh.PivotTables.Count > 0 Then
    
            'Loop through all the pivots on the sheet
            For Each pvt In sh.PivotTables
        
                Call ExpandPivotFields
        
            Next pvt
            
        End If

 

Sounds like the "ThisWorkbook" element will need to be changed so you can save the routine in a separate workbook to run each time.

 

@Wyn Hopkins 

 

As I've never written an Excel Macro before, I'm not quite following how to extend that code to loop through all the sheets in a Workbook.  

I tried the Macro you linked, and it does work.  To execute it, I just need to open the Workbook containing that Macro (which I downloaded from your supplied link).  Then if I open one of my Workbooks, and go to Developer->Macros, I can select the Macro I need (since the Workbook with the Macro is open, it can access its Macros), and Run.  But, as you know, this works for the open sheet only.

 

The structure of the Macro is as follows:

**************

Option Explicit

 

Sub Some_Name_1( )

[Several lines of code]

End Sub

 

Sub Some_Name_2( )

[Several lines of code]

End Sub

***********************

 

So exactly where do I insert the above code into your code to create the needed loop?

 

[I also posted that question on the link itself, asking them how to extend their code to loop through all the sheets in a Workbook.]

@Wyn Hopkins 

 

As I've never written an Excel Macro before, I'm not quite following how to extend that code to loop through all the sheets in a Workbook.  

I tried the Macro you linked, and it does work.  To execute it, I just need to open the Workbook containing that Macro (which I downloaded from your supplied link).  Then if I open one of my Workbooks, and go to Developer->Macros, I can select the Macro I need (since the Workbook with the Macro is open, it can access its Macros), and Run.  But, as you know, this works for the open sheet only.

 

I see your Macro has the following statement:

 

Call ExpandPivotFields

 

But how will this call the specific Macro I need, since the link contains a package consisting of several different Macros?:

 
 
 
 
 
 
 

Screen Shot 2021-01-17 at 4.43.23 PM.png


Should I substitute the actual name of the Macro I want, which is "Expand_Entire_RowField" for "ExpandPivotFields"?

Alternately, the structure of the Macro I need is:

**************

Option Explicit

 

Sub ExpandEntireRowField ()

[Several lines of code]

End Sub

 

***********************

 

So could I insert the above code into your code to create the needed loop?  If so, how would I do that?

 

[I also posted that question on the link itself, asking them how to extend their code to loop through all the sheets in a Workbook.]

Hi
Unfortunately if you're not familiar with Macros I wouldn't recommend this example being your introduction. It's pretty complicated especially given you want to call a different workbook compared to the one the Macro is located in.

Note: The code refers to "ThisWorkbook" which is not what you want to use

To reply to your question
- Should I substitute the actual name of the Macro I want, which is "Expand_Entire_RowField" for "ExpandPivotFields"
The answer is Yes

Unfortunately I don't have the time available to write a complete solution for you
Best Response confirmed by byte99 (Occasional Contributor)
Solution

Hi @byte99

Not my best work but something like this.  You could either put this code in your personal macro workbook (and assign a short cut key, or assign it to Quick Access Toolbar button) for repeated use.   Or (simpler to start with)  press Alt F11 on your Pivot Table file and paste this code into the Workbook windowimage.png

Sub ExpandAll()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long
Dim sh As Worksheet
Dim wkbkTarget As Workbook


Application.ScreenUpdating = False

Set wkbkTarget = ActiveWorkbook

For Each sh In wkbkTarget.Worksheets

        'If there is any pivot table on the sheet
        If sh.PivotTables.Count > 0 Then
    
    
    
        sh.Activate
       
      
        
       'Expand the lowest position field in the Rows area
'that is currently expanded (showing details)
 For Each pt In sh.PivotTables
        
               
        
           

  'Create reference to 1st pivot table on sheet
  'Can be changed to reference a specific sheet or pivot table.
 ' Set pt = ActiveSheet.PivotTables(1)

  'Count fields in Rows area minus 1 (last field can't be expanded)
  iFieldCount = pt.RowFields.Count - 1
  
  'Loop by position of field
  For iPosition = 1 To iFieldCount
    'Loop fields in Rows area
    For Each pf In pt.RowFields
      'If position matches first loop variable then
      If pf.Position = iPosition Then
        'Loop each pivot item
        For Each pi In pf.PivotItems
          'If pivot item is collapsed then
          If pi.ShowDetail = False Then
            'Expand entire field
            pf.ShowDetail = True
            'Exit the loop
            GoTo NextPT
          End If
        Next pi
      End If
    Next pf
  'If the Exit Sub line is not hit then the
  'loop will continue to the next field position
  Next iPosition
  
NextPT:
  
  Next pt
           
           End If
            Next sh
            
    Set wkbkTarget = Nothing
        
   Application.ScreenUpdating = True
   
End Sub


 

@Wyn Hopkins 

Thanks Wyn--works beautifully!