Results 1 to 3 of 3

Thread: Select all fields in PIVOT table

  1. #1

    Select all fields in PIVOT table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    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

  3. #3

    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 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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •