Remoing Pivot Items

[marshybid]

Hi All,

New to this forum, but hoping someone out there might be able to help me on this.

I am relatively inexperienced with VBA, but trying to get through things by trial and error.

I have written a macro that will create 2 seperate pivot tables, each representing the raw data in slightly different ways.

Here is the code for one of the pivot tables (I have also set the Pivot Items to display in a sprcific order):

'// Create Pivot Table of current status against each LOS/Job Number/Job Title and Applicant
Sheets("Pivot 2").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R1C1:R8000C43", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="'Pivot 2'!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Pivot 2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("LOS")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Number")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Number").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Title")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Title").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Application Status"), "Count of Application Status" _
, xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status")
.Orientation = xlColumnField
.Position = 1
End With

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status")
.PivotItems("Auto-closed").Visible = False
.PivotItems("Cancelled").Visible = False
.PivotItems("Closed").Visible = False
.PivotItems("In process").Visible = False
.PivotItems("New").Visible = False
.PivotItems("On hold").Visible = False
.PivotItems("Open").Visible = False
.PivotItems("Pending Applicants").Visible = False
.PivotItems("Rejected").Visible = False
.PivotItems("Telephone Screen").Visible = False
.PivotItems("Withdrawn").Visible = False
.PivotItems("Offer Declined").Visible = False
End With
On Error GoTo 0

ActiveWorkbook.ShowPivotTableFieldList = False
Rows("5:5").Select
ActiveWindow.FreezePanes = True
Range("A2").Select
On Error Resume Next
ActiveSheet.PivotTables("PivotTable2").PivotFields("LOS").PivotItems _
("(blank)").Visible = False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("CV Submitted").Position = 1
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("1st Interview").Position = 2
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("2nd Interview").Position = 3
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("3rd Interview").Position = 4
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("4th Interview").Position = 5
' ActiveSheet.PivotTables("PivotTable2").PivotFields("Interviews").Position = 2
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offer of Intent").Position = 6
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offered").Position = 7
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offer Accepted").Position = 8
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Hired").Position = 9
On Error GoTo 0

In the final Pivot Table only the Pivot Items that I want are displayed, however the recipients can drop down the field filter and select the Pivot Items that I made visible = False!!

These items still remain in the Raw Data so I can't just remove unused pivot items.

I need the visible = false pivot items to be entirely hidden from the filters options in the pivot table.

I hope that this makes sense, if not please do request clarification.

Any help would be greatly appreciated.

Thanks,

Marshybid