+ Reply to Thread
Results 1 to 11 of 11

VBA to loop through all pivot items in a pivot field and copies the results in a sheet

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    34

    VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Good day,
    I am searching for a macro/vba code which does the following:
    1/ Loop through all items of a specific pivot table field (in the example: BASIN)
    2/ Copy then one after the other the corresponding result computed in sheet "Gap_Calculation" and transferred to sheet "GAP" into a summary sheet where all are stored.
    There are usually per item of BASIN 4 results to be calculated: one for market division A, one for market division In, one for market division PA and one for all these 3 market divisions.
    How to do this?
    Thanks a lot in advance
    Best regards
    Heinz
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-16-2014
    Location
    Amsterdam, Nederland
    MS-Off Ver
    Excel 2013
    Posts
    71

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Hi,

    An impressive set of formula's
    Why not adding Basin as Row in the pivot and adjust your formula's accordingly?
    Bas

    Any reputation is appreciated.

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    34

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Hello
    Thank you for your reply and suggestion.
    This is possible but will cause quite a lot of work as the original database has more than 200000 items (and I would prefer a more flexible solution).
    Further it will change the result.
    So I'd prefer to have a VBA looping through the BASIN and copying the result.
    Best regards
    Heinz

  4. #4
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    144

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Hi megaheinz,

    You can loop through each category using this code.

    Can u clarify that if marketdivision, customer aggregate3, and product group is same for different basin category then we need add and give the result???

    i have created the macro can u clarify it.

    Please Login or Register  to view this content.
    Ravikumar,

    * Please Add Reputation if solved.

  5. #5
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    34

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Dear Fshbet,

    Thank you very much for your quick reply and proposal.
    Answering your question:
    Marketdivision, customer aggregate3, and product group is different for different basin category. But this doesn’t matter because this is taken into account in the computation done (automatically) in sheet “gap_calculation”.
    I tried your code but it gives an error message “run time error 1004” for the code line “Pvtable.PivotFields("Basin").CurrentPage = PTItem.Name”.
    Probably my description of my problem was not fully clear, I apologize: Hereunder I try to do it better:

    1. The macro should select item1 (“AM AST”) from the pivotfield “Basin”.
    2. Then it should copy paste the corresponding values to “AM AST” (only values, no formulas) automatically computed and appearing in the sheet “Gap” in rows 3 (D3 to J3), 7 (D3 to J3), 11 (D3 to J3) & 15 (D3 to K3) to a new sheet, called “Summary”.
    3. Then the macro should unselect item1 but select the next item2 (“AM GAL”) from the pivotfield “Basin”.
    4. Then it should copy paste the corresponding values to “AM GAL” (only values, no formulas) automatically computed and appearing in the sheet “Gap” in rows 3 (D3 to J3), 7 (D3 to J3), 11 (D3 to J3) & 15 (D3 to K3) to this new sheet, called “Summary”, below the data of “AM AST”
    5. The macro should do this for all items of “Basin” including item “ALL”

    I thank you much for the time you have already devoted and devote to help me solving my problem.
    Best regards

    Heinz

  6. #6
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    144

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Hi megaheinz,

    Please check the attachment. Example1.xlsm
    If is ok for u Please add reputation.

  7. #7
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    34

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Dear fshbet,
    This looks perfect. Many, many thanks!
    Is it also possible to add the pivotitem "All" in the loop?
    Reputation will be added for sure.
    Best regards
    Heinz

  8. #8
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    144

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Hi Megaheinz,
    Thanks for your comments.

    Not inside the loop but we can define outside the loop. What is the purpose for "All"??

  9. #9
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    34

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Dear fshbet,
    The purpose of "All" is to have then the weighted average of all these (pivot)items AM AST, AM GAL, etc.
    It is helpful as a starting point and then to drill down by the other pivotitems to see from where the big gaps are coming compared to this weighted average.
    Best regards
    Heinz

  10. #10
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    144

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Hi Heinz,

    This will select all category in Basin Field
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    34

    Re: VBA to loop through all pivot items in a pivot field and copies the results in a sheet

    Dear fshbet, thank you once more. Best regards, Heinz

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate field or Items on Pivot
    By Akatecho in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-29-2015, 10:42 PM
  2. Select Multiple Pivot Field Items in VBA
    By JungleJme in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2013, 01:36 PM
  3. Multiple items on pivot page field to show on other pivot
    By o4008 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-16-2012, 09:11 AM
  4. Excel 2007 : Hide Items in Page Field (Pivot Tables)
    By delecto in forum Excel General
    Replies: 3
    Last Post: 07-05-2010, 02:52 AM
  5. Pivot Table - untick all items in row field
    By DD1 in forum Excel General
    Replies: 6
    Last Post: 05-03-2006, 02:17 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1