Dears,
I need your help (once again...) regarding my pivot Table.
Once the pivotTable is created, I cannot make filter on the date by "Date Filter".
Only the "value Filter" is available.
I tried several option but without success until now.
May I kindly ask you to help me on this matter.
Dim p_i As PivotItem
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("DataBase").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard
ActiveSheet.Name = "Booking Desk"
Set objField = objTable.PivotFields("CS_RESPONSIBLE")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("CLOSED_BY_CMA")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("RESPONSIBILITY_NAME")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("COMMENT_DESCRIPTION")
objField.Orientation = xlPageField
objField.Position = 1
Set objField = objTable.PivotFields("RESPONSIBILITY_NAME")
objField.Orientation = xlDataField
objField.Function = xlCount
ActiveSheet.PivotTables(1).Name = "BookingDesk"
ActiveSheet.PivotTables("BookingDesk").ManualUpdate = True
With ActiveSheet.PivotTables("BookingDesk").PivotFields("COMMENT_DESCRIPTION")
For Each p_i In .PivotItems
p_i.Visible = True
Next
For Each p_i In .PivotItems
If Not InStr(1, p_i.Value, "BK -", vbTextCompare) > 0 Then
p_i.Visible = False
End If
Next
End With
With ActiveSheet.PivotTables("BookingDesk").PivotFields("RESPONSIBILITY_NAME")
.PivotItems("Booking Desk").Visible = True
For Each p_i In .PivotItems
If p_i.Name <> ("Booking Desk") Then
p_i.Visible = False
End If
Next
End With
With ActiveSheet.PivotTables("BookingDesk").PivotFields("CS_RESPONSIBLE")
On Error Resume Next
.PivotItems("Adriana Ortigueira").Visible = True
.PivotItems("Caio Carvalho").Visible = True
.PivotItems("Daniel Alves").Visible = True
.PivotItems("Fábio Ignácio").Visible = True
.PivotItems("Ikaro Vieira").Visible = True
.PivotItems("Juliana Tenório").Visible = True
.PivotItems("Kleber Cardozo").Visible = True
.PivotItems("Lucas Silva").Visible = True
.PivotItems("Marcelle Bueno").Visible = True
.PivotItems("Nara Santos").Visible = True
.PivotItems("Pedro Cardoso").Visible = True
.PivotItems("Robson Santos").Visible = True
.PivotItems("Thiago Fernandes").Visible = True
.PivotItems("Cintia Migues").Visible = False
.PivotItems("Ronaldo Magueta").Visible = False
.PivotItems("Talita Santos").Visible = False
.PivotItems("André de Jesus").Visible = False
On Error GoTo 0
End With
ActiveSheet.PivotTables("BookingDesk").ManualUpdate = False
ActiveSheet.PivotTables("BookingDesk").ColumnGrand = False
ActiveSheet.PivotTables("BookingDesk").TableStyle2 = "PivotStyleDark16"
With ActiveSheet.PivotTables("BookingDesk")
.ColumnGrand = True
.RowGrand = False
End With
End Sub
Once I can able the multiple Filter, I need to create a "Date Filter" - "Last Week"
You can see screenshot enclosed.
Hope you will be able to help me
Regards
Date Filter.png
Bookmarks