Pivot table automation question

[eli5150]

Up until last week my code was flawless, however, this morning i rec'd some feedback that something went wrong.

My code was written to refresh 3 pivot tables and display 2 values per table.

Here is the problem, this morning our source CSV data had some new added values and this cause the pivot tables to refresh and add them to the displayed values. That procedure caused havoc to all the dependent formulas associated with them.

Below is my failed attempted solution to only allow 2 specific PivotItems to be displayed per table. The refresh still displays any new data that arrives.

Can someone help with a solution to only allow my specific PivotItems to be visible regardless of any future data that may show up later?



ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
With ActiveSheet.PivotTables("PivotTable6").PivotFields("TERM_DAY FORMAT")
.PivotItems("30AN45").Visible = True
.PivotItems("30AN60").Visible = True
End With
ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh

Range("U27").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
With ActiveSheet.PivotTables("PivotTable5").PivotFields("TERM_DAY FORMAT")
.PivotItems("L10AN45").Visible = True
.PivotItems("L10AN60").Visible = True
End With
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

Range("U35").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
With ActiveSheet.PivotTables("PivotTable2").PivotFields("TERM_DAY FORMAT")
.PivotItems("L51AN45").Visible = True
.PivotItems("L51AN60").Visible = True
End With

 

[Greg Truby]

(a) please use code tags when posting code and (b) here is a quick & dirty off-the-cuff example

sub foo()

dim pt as pivottable, pf as pivotfield, pi as pivotitem

set pt = ActiveSheet.PivotTables("PivotTable6")
set pf = pt.PivotFields("TERM_DAY FORMAT") 
pt.pivotcache.refresh   ' (edited in)
for each pi in pf.pivotitems
    select case pi.name
        case "30AN45", "30AN60"
              pi.visible = true
	case else
              pi.visible = false
    end select
next pi

set pf = nothing
set pt = nothing

NOTE: I'm currently cranking month-end stuff in Excel, so I cannot test this! But I think I have the syntaxes about right.

 

[eli5150]

Thanks it worked. However, it runs longer....but the end result is perfect.

thanks again for your time and advice.