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.

Pivot Report - Filter to display single value else display all

Discussion in 'VBA Macros' started by Sanoj, Mar 30, 2017.

  1. Sanoj

    Sanoj Member

    Messages:
    50
    Dear All,

    I have created a macro based pivot table however, I am looking for a vba code which should work on pivot's "Report Filter" and should display the value if the filter contains only single value in the drop down list else should stay as is with value as "All".

    Any help? I have attached the pivot filter screenshot for better understanding. Please let me know if anymore details required.

    Thanks and Regards,
    Sanoj Viswam

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Upload sample workbook, mirroring your actual set up.

    PivotTable code will largely depend on your data source (OLAP or standard Table).
  3. Sanoj

    Sanoj Member

    Messages:
    50
    Hi Please find the attached Macro working file. In the attached Pivot there is a sheet called Pivot.

    I have prepared a macro based Pivot table and it may grow to D, E, F etc. its not of a specific length. and I am trying to create another Pivot table next to it using macro but not sure how to put it 1+ next column. That is if the 1st pivot ends at E next pivot should begin from G.
    Also, the filter as described above... if its a single value should reflect the same else should be as default as "All", I have no clue how to do it :(

    Attached Files:

  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Hmm, give me bit of time to go through your code and understand the process and clean it up. There's few errors in the code and it errors out when it's run as is.

    To answer your question. You can use pivottable's TableRange2 property to find last column for the first pivottable.

    As for filter. Let me think on best way to do this.
  5. Sanoj

    Sanoj Member

    Messages:
    50
    @Chihiro thanks for the response.
  6. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    I'm having hard time what you are intending with your code.

    Can you walk me through steps?

    For an example you are deleting everything from "Report" sheet but the header. Then copying C2 from same sheet to "Parameter" sheet B6 (ie. blank value).
    Code (vb):
    If ActiveWorkbook.Sheets("Parameter").Range("C6").Value = "" Then
                MsgBox "Please Enter the ADT-File ID number in Parameter sheet C6."
                Sheets("Parameter").Select
                Range("C6").Select
                Exit Sub
    End If

    s = Worksheets("Parameter").Range("C6").Value

    Set ws = ActiveWorkbook.Sheets("Report")
    With ws
        LastRow = .Range("Y" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("A1:AR" & LastRow)
    End With

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=25, Criteria1:="<>" & s, Operator:=xlAnd
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False

        Sheets("Report").Select
        Range("A2").Select
    '    Sheets("Parameter").Select
    '    Range("C6").Select
    '    Selection.ClearContents
       Sheets("Report").Select
        Range("C2").Select
        Selection.Copy
    'Application.ScreenUpdating = True
       Sheets("Parameter").Select
        Range("B6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.CutCopyMode = False
    Then your code proceeds to create pivot table based on blank "Report" sheet with only header row and no data.
    Code (vb):
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Pivot").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Pivot"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("Pivot")
    Set DSheet = Worksheets("Report")


    'Define Data Range
    LastRowp = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRowp, LastCol)

    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
    TableName:="ADT_PivotTable")
    '.... continued
    What are the reason for these steps? It may be easier, if you can explain what operation needs to be performed on your source data and what end result should be.
  7. Sanoj

    Sanoj Member

    Messages:
    50
    Hello Chihiro,

    Apologise for the confusion, I have uploaded a new spread sheet. Basically what I am looking for is, in the attached file there are two sheets.
    1. Report
    2. Pivot

    I am looking for a macro which could display each five pivot filter i.e. A1:B5 if it has only a single value else should show the value as ALL.

    Hope all the mess is cleared now :)

    Attached Files:

  8. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Try something like below.
    Code (vb):
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pvF As PivotField
    Dim pvI As PivotItem
    Application.EnableEvents = False

    For Each pvF In Target.PageFields
        If pvF.PivotItems.Count > 1 And pvF.CurrentPage = "(All)" Then
            For Each pvI In pvF.PivotItems
                pvI.Visible = True
            Next
        End If
    Next

    Application.EnableEvents = True
    End Sub
  9. Sanoj

    Sanoj Member

    Messages:
    50
    No its not running. Attached the file the code is in Module 1. :(

    Attached Files:

  10. Sanoj

    Sanoj Member

    Messages:
    50
    Hello @Chihiro did you get a chance to look into it.
  11. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Code should be in Worksheet module (not in Module1). For the sheet which contains the pivottable.

    From the looks of it you are looking for something that's different than what I had originally in mind. This should do what you are looking for. However, this code will force all item to be visible even when specific items are selected for multi-item field. Is there instance where you need multiple-filter items selected and displayed by the user?

    Put below into Worksheet module for "Pivot" sheet to test.
    Code (vb):
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pvF As PivotField
    Dim pvI As PivotItem
    Application.EnableEvents = False

    For Each pvF In Target.PageFields
        If pvF.PivotItems.Count > 1 And pvF.CurrentPage = "(All)" Then
            For Each pvI In pvF.PivotItems
                pvI.Visible = True
            Next
        ElseIf pvF.PivotItems.Count = 1 Then
            pvF.PivotItems(1).Visible = True
            pvF.CurrentPage = pvF.PivotItems(1).Value
        End If
    Next

    Application.EnableEvents = True
    End Sub
    Thomas Kuriakose likes this.
  12. Sanoj

    Sanoj Member

    Messages:
    50
    May be I am doing something wrong, could you please attach the file if possible.
    Because I am still not getting any results for filter. All the pivot filters are still in default condition as ALL.
  13. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    You need to refresh the pivottable for the code to fire.
  14. Sanoj

    Sanoj Member

    Messages:
    50
    Sorry to respond late, I tried refreshing the pivote table but its not working.
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Upload the file where it isn't working.
  16. Sanoj

    Sanoj Member

    Messages:
    50
    Hello, File is attached and the code is in module.

    Attached Files:

  17. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Like I stated earlier. Code should go in Worksheet module and NOT standard module (i.e. Module1).

    upload_2017-4-11_7-38-15.png
  18. Sanoj

    Sanoj Member

    Messages:
    50
    Thanks, but I was looking for something which could go in module1 so that I could tweak and blend it with other existing code.
    Is there any other way to put it in module1
  19. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,675
    Without context I can't give you much.

    If you want to use the code in regular module, just put it into standard sub (not Worksheet_PivotTableUpdate event sub) and replace "Target.PageFields" with reference to specific PivotTable's PageFields.
  20. Sanoj

    Sanoj Member

    Messages:
    50
    Thanks but, was not of much help. I would look out in some other forums.

Share This Page