• 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...

  • 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

Sanoj

Member
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    6.9 KB · Views: 6
Upload sample workbook, mirroring your actual set up.

PivotTable code will largely depend on your data source (OLAP or standard Table).
 
Upload sample workbook, mirroring your actual set up.

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

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 :(
 

Attachments

  • CB07 - Macro2D.xlsm
    77.6 KB · Views: 3
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.
 
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:
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:
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.
 
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 :)
 

Attachments

  • Pivote filter.xlsx
    34 KB · Views: 3
Try something like below.
Code:
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
 
No its not running. Attached the file the code is in Module 1. :(
 

Attachments

  • Pivote filter.xlsm
    40.3 KB · Views: 2
Try something like below.
Code:
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
Hello @Chihiro did you get a chance to look into it.
 
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:
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
 
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.
 
Hello, File is attached and the code is in module.
 

Attachments

  • Pivote filter.xlsm
    38.8 KB · Views: 1
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.
 
Back
Top