Show all pivot items

provincial

New Member
Joined
Jul 28, 2006
Messages
49
Using the following code

For Each job In Sheets("JobCostReport($)").PivotTables("PivotTable1").PivotFields("Job Number") _
.HiddenItems
job.Visible = True
Next job

the code breaks on a job which is in the pivotitem dropdown list. Is this because the pivotitem is an 'old item' still in the list but with no data in the datasource, if so how can this be resolved?

.........I have turned off the item autosort option in the pivotfield, this also appears to break the code.

Cheers
Paul
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
You could try adding an error handler:

On Error Resume Next
~~your code
On Error GoTo 0

This may be a way of basically "skipping" over the old items... (not sure if it will work in the context of your goals)

Another try would be to clear the old items out first (there IS a reason why these are there but it escapes my right now what that reason is...)...

Rich (BB code):
Sub DeleteMissingItemsExcel2002All()
'prevents unused items in non-OLAP PivotTables
'For Excel 2002 and later
'Thank you to http://www.contextures.com/xlPivot04.html

'If unused items already exist,
  'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
  Next pt
Next ws

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top