Remove Pivot Items in Pivot Table

[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

 

[xlbo]

Not sure you can to be honst. If they are in teh base data, they are seld ctable in the pivot table. Toggling the visibility only changes what initially appears in the table - not what can be selected from any drop down lists..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

IMHO, this is a major shortcoming of PivotTables.

I'd suggest, rather, using MS Query as a parameter query.

Use a Primary Selection to query that data for Distinct occurences for the Second Selection. That list will be the ListFillRanges of a ComboBox. When the Second selection is made, run the final query, based on the TWO Selected Criteria. Very little code needed.

faq68-5829.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}