VBA help - an easy question
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.
|