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