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?
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.