Select all fields in PIVOT table

rm2010

New member
Joined
Oct 13, 2011
Messages
11
Reaction score
0
Points
0
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
 
Still struggling:

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 :smile: 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
 
Back
Top