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

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

Code:
Code:
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
    Sheets("Pivot").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
   
   
     Worksheets("Data").Activate
     Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
         Worksheets.Add
        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:
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.

Narayan
 
Back
Top