1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help- automating with VBA

Discussion in 'VBA Macros' started by Pedrogf, Jul 16, 2017.

  1. Pedrogf

    Pedrogf New Member

    Messages:
    2
    Hello,

    I need help with an huge amount of data that keep beeing refreshed. I need to develop a button that brings me the top 5 names that have the most sum amount of revenue and the sum of the amount of revenue of each name. thanks
  2. dan_l

    dan_l Active Member

    Messages:
    724
    i'd need to see the data.
  3. Pedrogf

    Pedrogf New Member

    Messages:
    2

    Attached Files:

  4. dan_l

    dan_l Active Member

    Messages:
    724
    If there's no need to get cute:
    1) put your data in a dynamic named range
    2) create 2 pivot tables with the desired sort on and set filter to top X
    3) Using getpivotdata() set up grids on your output sheet
    4) simple....super simple....vba to refresh the pivot tables based on the named range

    Code (vb):

    Sub RefreshPivots()
    Dim pt As PivotTable
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Sheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

           
    End Sub
     

    Attached Files:

Share This Page