excel_training_wheels
Board Regular
- Joined
- Aug 25, 2005
- Messages
- 193
Hi-
I am trying to add a macro to update my pivot table based on dates I input. I input June 1 2011, and my formula converts it into the proper pivot table structure:
[2011].[Quarter 1].[June].[1]
I then have this as a VBA module
Sub ChangeDate()
Sheets("Test").Activate
For i = 1 To 1
With Sheets("Test").PivotTables("Test" & i)
.PivotFields("[BKGDate]").AddPageItem "[BKGDate].[All BKGDate]." & Sheets("Select").Cells(1, 7).Value, True
End With
Next i
End Sub
This works great. My goal, though, is:
1. Have the code filter the pivot table to year to date - based on the date I input (in this case Jan 1 - Jun 1)
2. Same date range for last year (for a year on year comparison).
Any ideas?
I am trying to add a macro to update my pivot table based on dates I input. I input June 1 2011, and my formula converts it into the proper pivot table structure:
[2011].[Quarter 1].[June].[1]
I then have this as a VBA module
Sub ChangeDate()
Sheets("Test").Activate
For i = 1 To 1
With Sheets("Test").PivotTables("Test" & i)
.PivotFields("[BKGDate]").AddPageItem "[BKGDate].[All BKGDate]." & Sheets("Select").Cells(1, 7).Value, True
End With
Next i
End Sub
This works great. My goal, though, is:
1. Have the code filter the pivot table to year to date - based on the date I input (in this case Jan 1 - Jun 1)
2. Same date range for last year (for a year on year comparison).
Any ideas?
