Hi RE Miller,
When grouping dates by quarters in a pivot table, is there any way to
modify
which months are considered Q1, Q2, etc? I'd like Q1 to reflect dates from
July-September rather than Jan-March. Is this possible?
Excuse me for butting in late - that's what I do these days
Another way to do this is to add the following code to the ThisWorkbook
module.
This works because you can change the caption of grouped dates from 'Qtr1'
to 'Q3'. Changing the caption manually is a bit of a waste of time, because
the captions will revert every time the pivot table is refreshed. The code
captures the update event for all pivot tables in the workbook and checks
for a 'Quarters' field. If it finds the field, it then changes all the
captions to Fin Year quarters.
Not extensively tested, but should work ok.
Ed Ferrero
www.edferrero.com
'=================================================
Option Explicit
' IsUpdated is a global variable that lets us know
' if the Pivot Table Update event has been triggered by the user
' or by our code in this module
Private IsUpdated As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target
As PivotTable)
If Not IsUpdated Then
If SetFiscalQtr(Target) Then
Application.StatusBar = "Fiscal Year Quarters Set"
IsUpdated = False
End If
End If
End Sub
Private Function SetFiscalQtr(pt As PivotTable) As Boolean
' Function to set Quarters From Excel default to fiscal year
' i.e. Q1 = Jul, Aug, Sep etc.
If FieldExists(pt, "Quarters") Then
IsUpdated = True
With pt.PivotFields("Quarters")
' could check to see if we have already
' renamed the quarters, or just error and keep
' going
On Error Resume Next
.PivotItems("Qtr3").Caption = "Q1"
.PivotItems("Qtr4").Caption = "Q2"
.PivotItems("Qtr1").Caption = "Q3"
.PivotItems("Qtr2").Caption = "Q4"
.AutoSort xlAscending, "Quarters"
' turn error checking back on
On Error GoTo 0
End With
SetFiscalQtr = True
Else
SetFiscalQtr = False
End If
End Function
Private Function FieldExists(pt As PivotTable, strField As String) As
Boolean
' checks if strField is a Pivot Field in the Pivot Table pt
' true if field exists
Dim fld As PivotField
FieldExists = False
For Each fld In pt.PivotFields
If fld.Name = strField Then
FieldExists = True
Exit Function
End If
Next
End Function
