Stop Pivot Table from changing Top 3 report even after changing the field

Hello Sanjay
Can you please post a sample workbook ?
Gowflow

Sure,

In the given workbook, there is a sheet containing Data, and there is a sheet containing 2 Pivots.
In Pivot sheet, there is a Pivot for Top 3 Dealers, and another for Top 3 Customers.

If I don't want 2nd Pivot, and I make a change in first Pivot, field from Dealer to Customer, then entire list for Customers displays. What I want is, when I change the field from Dealers to Customers, it should maintain Top 3 condition, and display Top 3 Customers when I replace Dealers with Customer field. I should not be making another Pivot for Customers.

Regards,
Sanjay.
Top 3 Values - Pivot.xlsx

Looks like you have to do it once for both cases. Once done you can change one to the other.

Well we can if you are interested via VBA.
Gowflow

Sure,
That will be a good help. I know some VBA, so I should be able to crack the code you send. Can you help?
Sanjay.

Well here is the code that I embeded in a module in the attached workbook. I also created 2 options buttons that you can toggle between Dealer and Customer to make your choice. To add options I created a combo dropdown where you can select what top you want Top 10 Top 5 … Top 3 is the default.

Here is the code

Option Explicit

Sub Top3(sSource As String)
On Error Resume Next

Dim WS As Worksheet
Dim Pvt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim Pfilter As PivotFilter

'MsgBox sSource
Application.ScreenUpdating = False

Set WS = ActiveSheet

For Each Pvt In WS.PivotTables
    If Pvt.Name = "PivotTable3" Then
        
        '---> Clear All Filters and fields and Data
        Pvt.ClearAllFilters
        
        For Each pf In Pvt.PivotFields
            For Each df In Pvt.DataFields
                df.Orientation = xlHidden
            Next df
            If Not pf Is Nothing Then pf.Orientation = xlHidden
        Next pf
        
        '---> Create Field as selected
        Pvt.AddFields RowFields:=sSource
        Set pf = Pvt.PivotFields(sSource)
        pf.LabelRange = sSource
        
        Pvt.AddDataField Pvt.PivotFields("Quantity")
        Set df = Pvt.DataFields(1)
        df.LabelRange = "Total Quantity"
        df.LabelRange.HorizontalAlignment = xlRight
        df.NumberFormat = "#,##0 "
        
        '---> Filter Top N
        'pvt.PivotFields(sSource).PivotFilters.Add Type:=xlTopCount, DataField:=df, Value1:=3, Order:=xlDescending
        pf.PivotFilters.Add Type:=xlTopCount, DataField:=df, Value1:=Val(Sheets("Pivot").ComboBox1.Value), Order:=xlDescending
        pf.AutoSort Order:=xlDescending, field:=df
        
        Pvt.RowGrand = True
    End If

Next Pvt


Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Enjoy.

Gowflow
Top-3-Values---Pivot-V01.xlsm

Wonderful!! It works like a charm.
The two option buttons, finally gave it a lift. Thanks, much needed solution.
(Sorry I got little too busy with support online, could not revert earlier).

Thanks a ton!!

Your welcome glad I could help. Please feel free to ask any other question you may need help with and case you want my participation please put a link in this question as I will keep monitoring it. I am thinking maybe you need to adapt this to your real data or any other issue.
Gowflow

Sure. How to put that link you are saying...

well when you create a new question you simply copy the address Http of that question and paste it in here. I will take it from there. You can put in the question description that this is a follow-up on previous question etc ...
Gowflow

Thanks. 😊