Select all fields in PIVOT table

[rm2010]

Hi

I'm a simple guy and I have a pivot table. The combination is not working out well.

I click "show field list". Now I can build my pivot table by selecting the fields I want to see. Great.

I am now recording in a macro called "ResetTable". My current approach is

1) Record macro
2) Open field list
3) Manually select and unselect each field item.
4) Stop macro

There are about 90 fields which require the selecting and unselecting. It's quite tedious and the macro takes nearly 2minutes to complete the macro. I can't imagine that I have out-witted excel, so can someone tell me how to find the "Select All" button when I view the field list. Surely there must be an option to select all fields?

Thanks all
RM

[Sixthsense]

The 3rd point consumes huge time because record macro inputs code for each field selection.

Instead of selecting the Fields manually loop through the PT Field List so that the selection process will occur in high speed.

Refer the below link to get some understanding about how to loop through the PT fields

http://jamiche.blogspot.in/2011/04/v...-in-pivot.html

[rm2010]

Thank you sixth

Sorry - i don't understand that. I am really simple on excel hence why the blog will confuse me. As a simple guy all I want to do is ctrl X my current macro (as per below) and ctrl V the genius looping macro that saves the run time on below. Can you help? To allow the text to paste I've deleted a random part in the middle from the macro below.


Sub Reset_Table()
'
' Reset_Table Macro
'
'
Range("C11").Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("0")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Symbol")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Name")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Yesterday's close")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Current price")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Price change")
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Percentage change")
.Orientation = xlRowField
.Position = 7
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Price currency")
.Orientation = xlRowField
.Position = 8
ActiveSheet.PivotTables("PivotTable5").PivotFields("Total assets (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Creditors; short (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Creditors; long (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Creditors; other (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Insurance funds (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Subordinated loans (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Total liabilities (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"Ordinary capital; reserves (m)").Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Prefs; monorities (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Total equity (m)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Net Assets value - Basic"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Net Assets value - Diluted" _
).Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Cash flow PS").Orientation _
= xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Retained Profit PS"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Turnover PS").Orientation _
= xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Price To cash Flow PS"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"Price To Retained Profit PS").Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("Price to cash PS"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"Price to Working Capital PS").Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable5").PivotFields("P/CFPS-EPS").Orientation = _
xlHidden
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=-27
End Sub