• 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


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to Filter Date

Neeraj Dhiman

New Member
Hi All,

I am creating so many reports in a day and for one of my report i have create Pviot VBA to make Pivot quickly however i am not able to filter the Allocation date as current quarter. Can any one help to me define or put filter as current quarter dates only

Sub Create_Pivot()
    Dim PCache As PivotCache, LastRow As Long, pt As PivotTable
          'If "Pivot" worksheet already exists, delete it
    On Error Resume Next
    Application.DisplayAlerts = False
    On Error GoTo 0
    Application.DisplayAlerts = True
     Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
        ActiveSheet.Name = "Pivot"
        ActiveWindow.DisplayGridlines = False
     Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("A1"), TableName:="PivotTable1")
     'Select fields for PivotTable
     ActiveWorkbook.ShowPivotTableFieldList = True
     With pt.PivotFields("Allocation Date")
         .Orientation = xlPageField
         .Position = 1
         .PivotItems("(blank)").Visible = False
    End With
     With pt.PivotFields("Lifecycle")
         .Orientation = xlPageField
         .Position = 1
         ActiveSheet.Range("B2") = "Demand Met"
     End With
     'With pt.PivotFields("Category")
      '   .Orientation = xlColumnField
       '  .Position = 1
     'End With
     With pt.PivotFields("Service Line")
         .Orientation = xlColumnField
         .Position = 1
     End With
    With pt.PivotFields("Portfolio")
         .Caption = "Portfolio"
         .Orientation = xlRowField
         .Position = 1
     End With
     'With pt.PivotFields("Allocation Date")
     '    .Orientation = xlRowField
     '    .Position = 2
     'End With
     'pt.AddDataField pt.PivotFields("Portfolio"), "Count of Protfolio", xlCount
     With pt.PivotFields("Portfolio")
        .Caption = "Count of Portfolio"
        .Orientation = xlDataField
        .Function = xlCount
     End With
Last edited by a moderator:

Neeraj Dhiman

New Member


Excel Ninja
Hi ,

Any time you use the dot notation , you have to make sure that the object you are referring to permits use of the methods or properties you have coded.

If you can upload the complete workbook with the data and the code in it , it will be easier to pinpoint the problem immediately.