Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to show the top N value from Pivot Table and make the results appear in just 1 sheet when N change?


I'm writing an Excel VBA code to show the top N value (country related information).

I'm having a problem as I want all the N value to appear in just 1 united source (1 sheet) when it changes, the issue is now the results appear in separated sheet.

Is there anyway we can make this work by using Excel VBA?

In the attachment, I added a button with assigned macro (sheet 2). When having this button clicked, you can see the output shown in separated sheet (sheet10,11,12). BUT the desired output should be in 1 united sheet (example in Desired Output sheet).

Attached the excel file for reference (the code included). Thanks a lot in advance for your advice.




Selected Answer


You can use the worksheet Change event to detect a change in a cell (containing N) then filter the pivot table based on that.

In the first attached file (and code below), I've put that value in B1. To prevent macro failure, I've also set data validation on that cell so you can only enter an integer between 1 and 200 (since there are currently only 195 countries) 

This code is behind Sheet2 (renamed Pivot in the second fileand I've added comments so you can see the steps (plus B1 is shown in bold, so you can change that if needed):

Private Sub Worksheet_Change(ByVal Target As Range)

' do nothing if a cell other than B1 changed
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

' prevent pivot table filter changes re-triggering this procedure
Application.EnableEvents = False

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country name")
    'remove existing filter
    ' apply a filter based on cell B1
    .PivotFilters.Add2 Type:=xlTopCount, _
    DataField:=ActiveSheet.PivotTables("PivotTable1"). _
    PivotFields("Sum of Amount"), Value1:=Target.Value

End With
' renable events
Application.EnableEvents = True

End Sub


Given your revised file (and question), I've moved the code in the sub  details (you copied from elsewhere I believe) to a standard module in the SECOND file below and modified it as below (with the event code above too). The Output sheet has only row 1 (at present) but when the button on the sheet renamed Pivot is clicked,  it will now collect the top N data (highest country first) and paste the detail into a single Output sheet (which is renamed to show when it was done) then show it. Comments are shown for your guidance:

Sub detail()
    Dim c As Range
    Dim LstRw As Long, Done As String
    'clear existing report detail but keep header row
    Sheet4.UsedRange.Offset(1, 0).Delete
    ' stop "want to save" alerts etc
    Application.DisplayAlerts = False
    ' loop through pivot
    With Sheet2.PivotTables("PivotTable1")
       For Each c In .DataBodyRange.Resize(, 1)
        'determine last row
          LstRw = Sheet4.Range("A" & Rows.Count).End(xlUp).Row
          'reveal data
          c.ShowDetail = True
          ' copy autoselected data (less headers) at end of Sheet4
          Selection.Offset(1, 0).Copy
          Sheet4.Range("A" & LstRw + 1).PasteSpecial Paste:=xlPasteAll
          ' delete the details sheet
       Next c
    End With
    Application.DisplayAlerts = True
    ' create text for time/date
    Done = Replace(Left(CStr(Time), 5), ":", "") & "hrs " & Replace(CStr(Date), "/", "-")
    'show output sheet and rename (with time/date)
    With Sheet4
        .Name = "Output " & Done
        .UsedRange.BorderAround (xlSolid)
        .Cells(2, 1).Select
    End With
    MsgBox "Here's the detail for the Top " & Sheet2.Range("B1") & " at " & Done
End Sub

Note that the Top N  pivot table is now sorted with the highest Amount value first (likewise for the Output detail).

Hope this solves your problem.



Hi John, Thank you very much for your response. But the desire output that I want to show is actually a little bit different. I have adjusted in my original post with more details information & re-attached the sample file. Could you please have a look & advise?  Thank you again.
joevn93 (rep: 4) Apr 15, '22 at 2:54 am
Joe. I'll try to find time later today to look. Next time, please make your original question clear first time if possible. 
John_Ru (rep: 3222) Apr 15, '22 at 3:45 am
See revision to my Answer and the second file please
John_Ru (rep: 3222) Apr 15, '22 at 1:01 pm
Thanks a lot John for your help. Your answer solved my pain point now. Really appreciated. Have a great day!!!
joevn93 (rep: 4) Apr 18, '22 at 3:10 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login