This is probably an easy one but I cannot figure it out. I have a pivot
table that displays data by month. What I want to do is automatically
unselect the last month in the list, so if I refresh my table in the middle
of a month it will not show the current month. In my searching the group for
this answer I was able to find some code that automatically selects all items
within the PivotItem. Which will be useful the following month to ensure I
get all months but the current month. What I need is the code to unselect
the last month in the list. The code I was trying to modify to tell it to
unselect the last month is:

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(€œ200607€�).Visible = False

What do I need to have in place of the (€œ200607€�) to have it use the last
month in the list.


Any help is greatly appreciated and thanks in advanced.

To select the last value in a pivottable field, use the .PivotItems.Count
property:

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False

Hope this helps,

Hutch

"MarkM" wrote:

This is probably an easy one but I cannot figure it out. I have a pivot
table that displays data by month. What I want to do is automatically
unselect the last month in the list, so if I refresh my table in the middle
of a month it will not show the current month. In my searching the group for
this answer I was able to find some code that automatically selects all items
within the PivotItem. Which will be useful the following month to ensure I
get all months but the current month. What I need is the code to unselect
the last month in the list. The code I was trying to modify to tell it to
unselect the last month is:

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(€œ200607€�).Visible = False

What do I need to have in place of the (€œ200607€�) to have it use the last
month in the list.


Any help is greatly appreciated and thanks in advanced.

Try .PivotItems(.PivotItems.Count).Visible = False

HTH

Die_Another_Day
MarkM (at) wrote:
This is probably an easy one but I cannot figure it out. I have a pivot
table that displays data by month. What I want to do is automatically
unselect the last month in the list, so if I refresh my table in the middle
of a month it will not show the current month. In my searching the group for
this answer I was able to find some code that automatically selects all items
within the PivotItem. Which will be useful the following month to ensure I
get all months but the current month. What I need is the code to unselect
the last month in the list. The code I was trying to modify to tell it to
unselect the last month is:

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems("200607").Visible = False

What do I need to have in place of the ("200607") to have it use the last
month in the list.


Any help is greatly appreciated and thanks in advanced.

Thanks for the help Tom and D_A_D that works great.

As I get further into this project I have two other question maybe you can
help with. The code I found to select all months is:

'Selects all months
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Chart Data").PivotTables("PivotTable2")
Set pf = pt.PivotFields("Month")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

How can I change this to select all pivot tables on this sheet instead of
just pivottable2.

Also how could I clean up this code and make it work with the above. So it
can run for all pivot tables and unselect the last month:

'Un-selects the last month in the list (current month)
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False
End With

Thanks so much.

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt in Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

HTH

Die_Another_Day

MarkM (at) wrote:
Thanks for the help Tom and D_A_D that works great.

As I get further into this project I have two other question maybe you can
help with. The code I found to select all months is:

'Selects all months
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Chart Data").PivotTables("PivotTable2")
Set pf = pt.PivotFields("Month")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

How can I change this to select all pivot tables on this sheet instead of
just pivottable2.

Also how could I clean up this code and make it work with the above. So it
can run for all pivot tables and unselect the last month:

'Un-selects the last month in the list (current month)
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False
End With

Thanks so much.

Thanks for the code. I tried running this and I get an error 13 type
mismatch on this line:

If pi.Position = pf.PivotItems.Count Then

Any thoughts as to why.

Thanks for you help.

"Die_Another_Day" wrote:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt in Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

HTH

Die_Another_Day

MarkM (at) wrote:
Thanks for the help Tom and D_A_D that works great.

As I get further into this project I have two other question maybe you can
help with. The code I found to select all months is:

'Selects all months
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Chart Data").PivotTables("PivotTable2")
Set pf = pt.PivotFields("Month")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

How can I change this to select all pivot tables on this sheet instead of
just pivottable2.

Also how could I clean up this code and make it work with the above. So it
can run for all pivot tables and unselect the last month:

'Un-selects the last month in the list (current month)
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False
End With

Thanks so much.

No idea why it failed, when I ran it, it worked fine; however you can
try the following code.

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

For Each pt in Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.PivotItems(pf.PivotItems.Count).Visible = False
Next pt

HTH

Die_Another_Day

MarkM (at) wrote:
Thanks for the code. I tried running this and I get an error 13 type
mismatch on this line:

If pi.Position = pf.PivotItems.Count Then

Any thoughts as to why.

Thanks for you help.

"Die_Another_Day" wrote:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt in Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

HTH

Die_Another_Day

MarkM (at) wrote:
Thanks for the help Tom and D_A_D that works great.

As I get further into this project I have two other question maybe you can
help with. The code I found to select all months is:

'Selects all months
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Chart Data").PivotTables("PivotTable2")
Set pf = pt.PivotFields("Month")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

How can I change this to select all pivot tables on this sheet instead of
just pivottable2.

Also how could I clean up this code and make it work with the above. So it
can run for all pivot tables and unselect the last month:

'Un-selects the last month in the list (current month)
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False
End With

Thanks so much.

Thanks so much for you help I was able to figure this out on my own. The
error was caused because I had one item not selected. To get around this I
inserted the code to select all items first then I run your code. So it
looks lik ethis:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt In Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
pi.Visible = True
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

"MarkM" wrote:

Thanks for the code. I tried running this and I get an error 13 type
mismatch on this line:

If pi.Position = pf.PivotItems.Count Then

Any thoughts as to why.

Thanks for you help.

"Die_Another_Day" wrote:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt in Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

HTH

Die_Another_Day

MarkM (at) wrote:
Thanks for the help Tom and D_A_D that works great.

As I get further into this project I have two other question maybe you can
help with. The code I found to select all months is:

'Selects all months
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Chart Data").PivotTables("PivotTable2")
Set pf = pt.PivotFields("Month")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

How can I change this to select all pivot tables on this sheet instead of
just pivottable2.

Also how could I clean up this code and make it work with the above. So it
can run for all pivot tables and unselect the last month:

'Un-selects the last month in the list (current month)
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False
End With

Thanks so much.

You can shorten the code a little like this if you would like.
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt In Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
pi.Visible = True
If pi.Position = pf.PivotItems.Count Then pi.Visible = False
'Else it's already visible
Next pi
Next pt

Die_Another_Day
MarkM (at) wrote:
Thanks so much for you help I was able to figure this out on my own. The
error was caused because I had one item not selected. To get around this I
inserted the code to select all items first then I run your code. So it
looks lik ethis:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt In Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
pi.Visible = True
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

"MarkM" wrote:

Thanks for the code. I tried running this and I get an error 13 type
mismatch on this line:

If pi.Position = pf.PivotItems.Count Then

Any thoughts as to why.

Thanks for you help.

"Die_Another_Day" wrote:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


For Each pt in Sheets("Chart Data").PivotTables
Set pf = pt.PivotFields("Month")
For Each pi In pf.PivotItems
If pi.Position = pf.PivotItems.Count Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pt

HTH

Die_Another_Day

MarkM (at) wrote:
Thanks for the help Tom and D_A_D that works great.

As I get further into this project I have two other question maybe you can
help with. The code I found to select all months is:

'Selects all months
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Chart Data").PivotTables("PivotTable2")
Set pf = pt.PivotFields("Month")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

How can I change this to select all pivot tables on this sheet instead of
just pivottable2.

Also how could I clean up this code and make it work with the above. So it
can run for all pivot tables and unselect the last month:

'Un-selects the last month in the list (current month)
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Month")
.PivotItems(.PivotItems.Count).Visible = False
End With

Thanks so much.