Set dynamic dates for Group Selection in Excel Pivot Table

Question

I have a data-set that I pull from TFS that grabs data from the past year (@Today-365). The data is grabbed by Excel and then used to populate pivot tables which are grouped by a date period of 28 days. I would like to automate the group-dates but can't get my code to work or find good examples on the 'Net.

Here's what I've got so far...

Sub SetStartDate()
'   Sets the date for the Start Date, End Date, and Groupings for the Date Periods
'

Dim wbk As Workbook
Dim ws As Worksheet
Dim pvt As PivotTable
Dim pvtFld As PivotField
Dim startDate As Date
Dim endDate As Date
Dim rowLabel As String

'Application.ScreenUpdating = False    ' Turn on Later

Set wbk = ActiveWorkbook
Set ws = wbk.ActiveSheet
Set startDate = Today() 'incomplete
Set endDate = Today()-365days 'incomplete

For Each pvt In ws.PivotTables
    Debug.Print "Pivot Name = " & pvt.name
    Debug.Print "Pivot Field Count = " & pvt.PivotFields.Count

    For Each pvtFld In pvt.RowFields
        Debug.Print "Pivot Field = " & pvtFld.name  ' Name of Pivot field
        rowLabel = pvtFld.name
        Debug.Print "Row Label = " & rowLabel
'THIS LINE DOESN'T WORK, BUT THIS IS WHERE I RUN INTO TROUBLE IN MY CODE
        pvtFld.Selection.Group start:=CLng(startDate), End:=CLng(endDate), By:=28, Periods:=Array(False, False, False, True, False, False, False)

    Next pvtFld
Next pvt

'Application.ScreenUpdating = True    ' Remove comment later

End Sub

As my comment in the middle states, I am not sure where to go after figuring out what the rowField name is.

Sam · Accepted Answer · 2015-05-27 20:30:55Z

I stumbled across my own solution a few minutes after posting - hopefully, this benefits someone else....

Sub SetStartDate()
'   Sets the date for the Start Date, End Date, and Groupings for the Date Periods

Dim wbk As Workbook
Dim ws As Worksheet
Dim pvt As PivotTable
Dim pvtFld As PivotField
Dim startDate, endDate As Long
Dim pivotName, rowLabel As String

Application.ScreenUpdating = False

Set wbk = ActiveWorkbook
Set ws = wbk.ActiveSheet
    startDate = ws.Range("P2").Value    ' Location of StartDate in Spreadsheet
    endDate = ws.Range("P1").Value  ' Location of EndDate in Spreadsheet

For Each pvt In ws.PivotTables
    For Each pvtFld In pvt.RowFields
        Debug.Print "Pivot Field = " & pvtFld.name
        rowLabel = pvtFld.name
'        Debug.Print "Row Label = " & rowLabel
        pvtFld.DataRange.Group start:=startDate, End:=endDate, By:=28, Periods:=Array(False, False, False, True, False, False, False)
    Next pvtFld

Next pvt

Application.ScreenUpdating = True

End Sub

Collectives™ on Stack Overflow

Set dynamic dates for Group Selection in Excel Pivot Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related