+ Reply to Thread
Results 1 to 11 of 11

Dynamically sort a pivot table

  1. #1
    Forum Contributor
    Join Date
    05-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    186

    Dynamically sort a pivot table

    Sorry, this is going to get wordy.

    I am trying to figure out if there is some VBA code to sort Pivot tables based on the selection of a drop down. I basically want to sort the pivot table largest to smallest on the column that corresponds to that month. I.e. a user selects May from the drop down I would like to sort the PT on “Sum of YTD Billings May (USD)” [it will always be “Sum of YTD Billings “&left(month,3)&” (USD)”.

    I would like it to simulate right clicking on $AQ$6 and selecting [sort>sort largest to smallest].

    The absolute reference will not move so Jan-Dec will always be AM6-AX6 if that makes this easier.
    I can conceptually figure out how to get myself to the cell:
    I can just hlookup $AM$3-$AX$6 for “sum of YTD billings “&left(dropdown,3)”&” (USD) then offset 3,0.
    Or start at the anchor of $AM$3 and offset 3,(a lookup value for the month Jan=1, Feb=2, etc…).

    The attached workbook is the general idea. It’s all dummy data but the column names and sheet names are the same. I have working formulas everywhere else, I was just hoping to get able to get the PT’s to sort for me for some dynamic changes made easy

    Any help would be amazing

    VBA to sort PT by columns.xlsx

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Dynamically sort a pivot table

    Hi smls,

    Here is your workbook.

    smls - VBA to sort PT by columns.xlsm

    I have taken the liberty of making one change to your Pivot Table. Instead of having the words "Sum of YTD Billings Jan (USD)" etc. for each column, they now read "January", February" etc. which matches your months list. It also looks far more pleasing.

    Attached to the Agency 1 sheet is an event macro which calls the PivotSort macro shown below when you make a change to Cell G3.

    Please Login or Register  to view this content.
    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    If several people have responded, when you reply please make it clear WHO you are responding
    to by mentioning their name.

  3. #3
    Forum Contributor
    Join Date
    05-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    186

    Re: Dynamically sort a pivot table

    David,

    Thank you so much for the response. I haven't gotten a chance to try it out yet in the real workbook, been so swamped with new requests that I was only able to get a look at it in the dummy file. I hate when people don't reply or mark a thread as solved so I wanted to let you know that I will let you know if it works as soon as I can get it into the workbook and try it out.

    Thanks again,
    Lewis

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Dynamically sort a pivot table

    You're welcome Lewis - please let me know when you get your head above!

    DAC

  5. #5
    Forum Contributor
    Join Date
    05-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    186

    Re: Dynamically sort a pivot table

    David,

    So I have had some time to mess around with it. First off it is exactly what I was looking for and it works great in the dummy book so thank you so much for that.
    I was able to drop the module in, my bad, getting an error in the module at for out of range
    Please Login or Register  to view this content.
    I looked through it and can't see the error. I made sure the PT name was PivotTable1, I made sure the month drop down was in the right cell. The sheet names match.
    Last edited by smls; 08-13-2015 at 02:50 PM.

  6. #6
    Forum Contributor
    Join Date
    05-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    186

    Re: Dynamically sort a pivot table

    Got it, totally my bad, brain was slow, had a dyslexic moment and transposed some letters but read it correctly for like 20 min. After I retyped everything it worked.

  7. #7
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Dynamically sort a pivot table

    You're welcome. Pleased you're pleased. Thanks for the encouraging comment on the Rep.

    DAC

  8. #8
    Registered User
    Join Date
    08-23-2018
    Location
    Wales
    MS-Off Ver
    13
    Posts
    3

    Re: Dynamically sort a pivot table

    Quote Originally Posted by David A Coop View Post
    You're welcome. Pleased you're pleased. Thanks for the encouraging comment on the Rep.

    DAC
    David, not sure if you will get this message, but I am looking to use your VBA above for the same thing, but I have a field in the Columns Section of my pivot table relating to Month/Year. How can you sort by a column dynamically (so you can select the date from a drop down box), and it sorts it based on the month/year which is listed as a column?

    Hope you get this message, but see you are an active poster. Thanks

  9. #9
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Dynamically sort a pivot table

    Hi SusanE,

    I don't think I can help without seeing a mock up of your excel workbook. I can't visualise what you are trying to achieve from what you have described.

    Regards,

    David

  10. #10
    Registered User
    Join Date
    10-05-2009
    Location
    Cincinnati, Oh
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Dynamically sort a pivot table

    Hi all,
    I'm trying to copy this code into my workbook but, I'd like to keep the selection cell on the sheet with the pivot table. Is that possible?
    Thanks!

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dynamically sort a pivot table

    Quote Originally Posted by cecarter74 View Post
    Hi all,
    I'm trying to copy this code into my workbook but, I'd like to keep the selection cell on the sheet with the pivot table. Is that possible?
    Thanks!
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. [SOLVED] Dynamically sort a pivot table
    By smls in forum Excel General
    Replies: 1
    Last Post: 08-11-2015, 04:57 PM
  2. Pivot Table Off of Dynamically Changing Data
    By dkim.ags in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2013, 06:36 PM
  3. [SOLVED] Dynamically Changing Position of Pivot Based on Size of Another Pivot Table
    By ggilzow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2013, 02:42 PM
  4. DYNAMICALLY data source for pivot table
    By Kalyan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2006, 11:10 PM
  5. Dynamically refreshing Pivot Table Range.
    By Rob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2006, 11:25 AM
  6. how do i link to a pivot table to get data dynamically
    By Michael001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-23-2006, 06:30 AM
  7. Pivot Table Dynamically Creating on Query
    By Brent in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2005, 12:05 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