pivot date field range
 

I'd like for a person to be able to select from two cells on a sheet to enter
a date range. I'd like for my pivot table date page field to then use those
two end cap dates as the range for which to not hide items. Possible?

In other words, right now, I have to ask the user to go into the field
settings, deselect (for example) a month of dates and select another month,
every time they want to change the scope of the table. I'd like to make it
easier by forcing the table to just on its own take two cells and use a macro
to use those dates as the start and stop of its range.

If anyone has some code that can accomplish this, would mean a lot towards
user convenience on this thing.

Thx.

--
Boris

pivot date field range
 

Maybe start with something like this:

Sub PvtDate()
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim endDate As Date
Dim startDate As Date

startDate = Sheets("Sheet5").Range("A1").Value
endDate = Sheets("Sheet5").Range("B1").Value
Set pvtTable = Sheets("Sheet5").PivotTables("PivotTable1")

On Error Resume Next
pvtTable.PivotFields("Date").PivotItems(endDate).V isible = True
For Each pvtItem In pvtTable.PivotFields("Date").PivotItems
If pvtItem.Name < startDate Or pvtItem.Name endDate Then
pvtItem.Visible = False
Else
pvtItem.Visible = True
End If
Next pvtItem
On Error GoTo 0
End Sub

Hope this helps
Rowan

BorisS wrote:
I'd like for a person to be able to select from two cells on a sheet to enter
a date range. I'd like for my pivot table date page field to then use those
two end cap dates as the range for which to not hide items. Possible?

In other words, right now, I have to ask the user to go into the field
settings, deselect (for example) a month of dates and select another month,
every time they want to change the scope of the table. I'd like to make it
easier by forcing the table to just on its own take two cells and use a macro
to use those dates as the start and stop of its range.

If anyone has some code that can accomplish this, would mean a lot towards
user convenience on this thing.

Thx.

pivot date field range
 

That is rock star performance, both in EXACTLY what I wanted, as well as turn
around. Thanks very much! I've never been good with 1) naming of items in
pivots in VBA and 2) loops. Tremendous thanks.
--
Boris


"Rowan Drummond" wrote:

Maybe start with something like this:

Sub PvtDate()
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim endDate As Date
Dim startDate As Date

startDate = Sheets("Sheet5").Range("A1").Value
endDate = Sheets("Sheet5").Range("B1").Value
Set pvtTable = Sheets("Sheet5").PivotTables("PivotTable1")

On Error Resume Next
pvtTable.PivotFields("Date").PivotItems(endDate).V isible = True
For Each pvtItem In pvtTable.PivotFields("Date").PivotItems
If pvtItem.Name < startDate Or pvtItem.Name endDate Then
pvtItem.Visible = False
Else
pvtItem.Visible = True
End If
Next pvtItem
On Error GoTo 0
End Sub

Hope this helps
Rowan

BorisS wrote:
I'd like for a person to be able to select from two cells on a sheet to enter
a date range. I'd like for my pivot table date page field to then use those
two end cap dates as the range for which to not hide items. Possible?

In other words, right now, I have to ask the user to go into the field
settings, deselect (for example) a month of dates and select another month,
every time they want to change the scope of the table. I'd like to make it
easier by forcing the table to just on its own take two cells and use a macro
to use those dates as the start and stop of its range.

If anyone has some code that can accomplish this, would mean a lot towards
user convenience on this thing.

Thx.

pivot date field range
 

Spoke a little too soon. It was able to limit the dates "down" take a range
that was already hidden, and then hide more based on my entering a narrowe
range. But when I entered a wider range in the same sheet, or change to a
different range, it doesn't seem to "unhide" all of the items and then hide
the appropriate ones. Seems to be stuck somehow with the more narrow range.
Anything you can think of that would cause this? I ask because I obviously
don't know the nuances of what exactly the macro is doing. Just generally
understand.

Thx for the further help.
--
Boris


"Rowan Drummond" wrote:

Maybe start with something like this:

Sub PvtDate()
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim endDate As Date
Dim startDate As Date

startDate = Sheets("Sheet5").Range("A1").Value
endDate = Sheets("Sheet5").Range("B1").Value
Set pvtTable = Sheets("Sheet5").PivotTables("PivotTable1")

On Error Resume Next
pvtTable.PivotFields("Date").PivotItems(endDate).V isible = True
For Each pvtItem In pvtTable.PivotFields("Date").PivotItems
If pvtItem.Name < startDate Or pvtItem.Name endDate Then
pvtItem.Visible = False
Else
pvtItem.Visible = True
End If
Next pvtItem
On Error GoTo 0
End Sub

Hope this helps
Rowan

BorisS wrote:
I'd like for a person to be able to select from two cells on a sheet to enter
a date range. I'd like for my pivot table date page field to then use those
two end cap dates as the range for which to not hide items. Possible?

In other words, right now, I have to ask the user to go into the field
settings, deselect (for example) a month of dates and select another month,
every time they want to change the scope of the table. I'd like to make it
easier by forcing the table to just on its own take two cells and use a macro
to use those dates as the start and stop of its range.

If anyone has some code that can accomplish this, would mean a lot towards
user convenience on this thing.

Thx.

pivot date field range
 

And here I thought I was a rockstar <g.

The problem is you have to have at least one item visible so the line of
code
pvtTable.PivotFields("Date").PivotItems(endDate).V isible = True
was meant to start out by unhiding the highest value but I didn't think
it through all that well. If your endDate is not actually a date in the
pivottable this will not work - as you have discovered. Try this revised
version:

Sub PvtDate()
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim endDate As Date
Dim startDate As Date

startDate = Sheets("Sheet5").Range("A1").Value
endDate = Sheets("Sheet5").Range("B1").Value
Set pvtTable = Sheets("Sheet5").PivotTables("PivotTable1")

On Error Resume Next
pvtTable.PivotFields("Date").Count
pvtTable.PivotFields("Date").PivotItems _
(pvtTable.PivotFields("Date").PivotItems.Count).Vi sible = True
For Each pvtItem In pvtTable.PivotFields("Date").PivotItems
If pvtItem.Name < startDate Or pvtItem.Name endDate Then
pvtItem.Visible = False
Else
pvtItem.Visible = True
End If
Next pvtItem
On Error GoTo 0
End Sub

Regards
Rowan

BorisS wrote:
Spoke a little too soon. It was able to limit the dates "down" take a range
that was already hidden, and then hide more based on my entering a narrowe
range. But when I entered a wider range in the same sheet, or change to a
different range, it doesn't seem to "unhide" all of the items and then hide
the appropriate ones. Seems to be stuck somehow with the more narrow range.
Anything you can think of that would cause this? I ask because I obviously
don't know the nuances of what exactly the macro is doing. Just generally
understand.

Thx for the further help.

pivot date field range
 

and as deserved, you retain rock star status. :)

Thanks again. Just out of curiosity, do you have any recommended reading
that taught you this stuff, or was it just playing around with these things?
I know some VBA, but never have gotten into macros for pvts, as mentioned.

--
Boris


"Rowan Drummond" wrote:

And here I thought I was a rockstar <g.

The problem is you have to have at least one item visible so the line of
code
pvtTable.PivotFields("Date").PivotItems(endDate).V isible = True
was meant to start out by unhiding the highest value but I didn't think
it through all that well. If your endDate is not actually a date in the
pivottable this will not work - as you have discovered. Try this revised
version:

Sub PvtDate()
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim endDate As Date
Dim startDate As Date

startDate = Sheets("Sheet5").Range("A1").Value
endDate = Sheets("Sheet5").Range("B1").Value
Set pvtTable = Sheets("Sheet5").PivotTables("PivotTable1")

On Error Resume Next
pvtTable.PivotFields("Date").Count
pvtTable.PivotFields("Date").PivotItems _
(pvtTable.PivotFields("Date").PivotItems.Count).Vi sible = True
For Each pvtItem In pvtTable.PivotFields("Date").PivotItems
If pvtItem.Name < startDate Or pvtItem.Name endDate Then
pvtItem.Visible = False
Else
pvtItem.Visible = True
End If
Next pvtItem
On Error GoTo 0
End Sub

Regards
Rowan

BorisS wrote:
Spoke a little too soon. It was able to limit the dates "down" take a range
that was already hidden, and then hide more based on my entering a narrowe
range. But when I entered a wider range in the same sheet, or change to a
different range, it doesn't seem to "unhide" all of the items and then hide
the appropriate ones. Seems to be stuck somehow with the more narrow range.
Anything you can think of that would cause this? I ask because I obviously
don't know the nuances of what exactly the macro is doing. Just generally
understand.

Thx for the further help.

pivot date field range
 

Glad to help.

I have John Walkenbach's "Excel 2000 Programming for Dummies" on my desk
as a quick reference and the rest is trial and error (usually more error
than trial). With Pivot Tables in particular it is often easiest to
start out using the macro recorder and then adapting the generated code.

Good luck
Rowan

BorisS wrote:
and as deserved, you retain rock star status. :)

Thanks again. Just out of curiosity, do you have any recommended reading
that taught you this stuff, or was it just playing around with these things?
I know some VBA, but never have gotten into macros for pvts, as mentioned.