How to set dynamic dates for pivot table grouping
Category: [Excel] Demo Available
How to set dynamic dates for pivot table grouping
Requirement:
The user is 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")
PT.DateRange.Group Start:=CLng(StartDate), End:=CLng(EndDate), By:=1, Periods:=Array(False, False, False, False, True, False, True)
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub
The user is getting a runtime error: 428 "Object doesn't support this property or method" and it highlights the PT.DateRange.Group line in bold above...
Solution:
Try this 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)
Obtained from the OzGrid Help Forum.
Solution provided by chirayuw.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create an Excel Pivot Table calculated field |
| How to group numbers in a pivot table |
| How to create a pivot table |
| How to work with Pivot Table multiple consolidation ranges |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.