+ Reply to Thread
Results 1 to 3 of 3

Get first 5 rows from pivot table and grand total of overall data

  1. #1
    Registered User
    Join Date
    02-19-2016
    Location
    US
    MS-Off Ver
    2007
    Posts
    74

    Unhappy Get first 5 rows from pivot table and grand total of overall data

    hi Experts,

    Am new to VBA and hope get some help here..

    I have worksheet with Pivot table having more than 100 rows and I want to copy only first 5 rows without altering the Grand Total data.

    I tried VBA code:
    Please Login or Register  to view this content.
    but the problem here is am getting Grand Total for the 5 rows only. Instead I want to retain the Grand Total as it is for the whole list.

    Also the Pivot Table rows are dynamic (in the sense rows will be growing or shrinking. Grand Total will not be in same row as in example).

    Am attaching sample sheet below:
    Table on the left is the Pivot Table with exact data and I want table on the right having top 5 rows with label and no change in Grand Total.
    PT.JPG

    I really appreciate any help provided.

    Regards,
    Richa

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Get first 5 rows from pivot table and grand total of overall data

    Within the pivot table's data area, this hides (not filters) entire rows below the top 5 rows. So the Grand Total should not change. Then it copy\pastes the visible data area and unhides the rows.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-19-2016
    Location
    US
    MS-Off Ver
    2007
    Posts
    74

    Re: Get first 5 rows from pivot table and grand total of overall data

    Wow AlphaFrog,

    You are simply amazing.. Thank You so much for helping and it worked.
    I also saw Your replies on other questions and Your logic and way of thinking really kept me wondering on how You answer these complex questions.

    Have another question though: Is there a way to create graph from the pivot table I created now and also on formatting the pivot table from VBA (something like transposing rows and columns)?
    As of now I recorded the actions and its almost working but wonder how to make code generic and to apply it everywhere with similar requirement rather than record script every time.

    Please let me know if there is any good ebooks available to study and implement on own (books with sample examples). I don't know much about VBA but definitely would like to learn and improve myself.

    Thanks
    Richa

+ 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. Replies: 6
    Last Post: 09-29-2015, 08:44 PM
  2. Different grand total in pivot table
    By arn0ldas in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-13-2015, 01:35 AM
  3. [SOLVED] Is it possible to have both Grand Total and Grand Average in a Pivot Table?
    By ANS in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 10-16-2012, 05:15 AM
  4. Pivot Table Grand Total Help
    By bmhalula in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-23-2012, 01:28 PM
  5. Pivot Table - Sub Total & Grand Total Filter
    By ramki in forum Excel General
    Replies: 1
    Last Post: 08-07-2009, 11:40 PM
  6. [SOLVED] Pivot Table - Grand Total for Rows
    By Joe in forum Excel General
    Replies: 3
    Last Post: 05-30-2006, 07:10 PM
  7. [SOLVED] % of Running Total to Grand Total in Pivot Table
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2005, 04:05 PM

Tags for this Thread

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