Show all pivot items

[provincial]

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

 

[xenou]

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...)...

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

 

[provincial]

Thanks.......resume next works fine for my application.

Cheers
Paul