Pivot Table Macro: Needs editing...

  • Thread starter Thread starter Jay Gustafson
  • Start date Start date
J

Jay Gustafson

Hello,

I am using a macro to hide all items in a pivot tables. It's exactly what I want, but this code leaves the last item in the list. What do I edit so even the last item is hidden?

Here is the code...

Hide Items -- Excel 97/Excel 2000

In previous versions of Excel, where there is no Show All checkbox, you can use programming to show or hide multiple fields. The following code hides all items, except the last item, in all row fields.

To hide column fields, change pt.RowFields to pt.ColumnFields.

Sub HidePivotItemsVisible()
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible = False Then
pi.Visible = True
Else
pi.Visible = False
End If
Next
Next
pf.AutoSort xlAscending, pf.SourceName
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
Please disregard this post.

There does have to be one selected.


Hello,

I am using a macro to hide all items in a pivot tables. It's exactly what I want, but this code leaves the last item in the list. What do I edit so even the last item is hidden?

Here is the code...

Hide Items -- Excel 97/Excel 2000

In previous versions of Excel, where there is no Show All checkbox, you can use programming to show or hide multiple fields. The following code hides all items, except the last item, in all row fields.

To hide column fields, change pt.RowFields to pt.ColumnFields.

Sub HidePivotItemsVisible()
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible = False Then
pi.Visible = True
Else
pi.Visible = False
End If
Next
Next
pf.AutoSort xlAscending, pf.SourceName
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

pivot table show items with a value 1
[VBA] Hiding rows in PivotTable 1
Macro Help in Pivot 2
Help with code for selecting pivot table field by typing in a cell 2
Grabbing the next column value in a pivot table 1
Filter a pivot table with large number of unique items in the filterlist 1
PivotField select all 3
Hide all PivotItems except for a specific Item 1

Back
Top