Setting Dynamic Dates for Pivot Table grouping

**NBVC** · Apr 4th 2018

Hi guys,

Where am I going wrong here.

I am trying to set dynamic dates for my Pivot Table Group.

Code

Dim StartDate As Date
Dim EndDate As Date
Dim PT As PivotTable
StartDate = Range("M1").Value
EndDate = Range("N1").Value
    Set PT = ActiveSheet.PivotTables("PivotTable3")
    [B]PT.DateRange.Group Start:=CLng(StartDate), End:=CLng(EndDate), By:=1, Periods:=Array(False, False, False, False, True, False, True)[/B]
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub

I am getting a runtime error: 428 "Object doesn't support this property or method" and it highlights the PT.DateRange.Group line in bold above...

chirayuw · Apr 4th 2018

Try this. I used website link below to figure out how to do it

http://www.globaliconnect.com/excel/...=79&Itemid=475

Code

Dim StartDate As String
Dim EndDate As String
Dim PT As PivotTable
Dim PFrng As Range


StartDate = CLng(Range("M1"))
EndDate = CLng(Range("N1"))
Set PT = ActiveSheet.PivotTables("PivotTable3")
Set PFrng = PT.PivotFields("POD/ETA").DataRange


PFrng.Cells(1).Group Start:=StartDate, End:=EndDate, By:=1, Periods:=Array(False, False, False, False, True, False, True)

Display More

**NBVC** · Apr 4th 2018

Hi,

I am getting error:13 Type Mismatch on the StartDate declaration...

Code

StartDate = CLng(Range("M1"))

**NBVC** · Apr 4th 2018

Nevermind,

My mistake.

Thanks very much. It worked.

Setting Dynamic Dates for Pivot Table grouping

Participate now!

Share