Pivot table automation question

E

eli5150

New Member
Joined
Apr 12, 2005
Messages
16
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
(a) please use code tags when posting code and (b) here is a quick & dirty off-the-cuff example
Code: 
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
:eek: NOTE: I'm currently cranking month-end stuff in Excel, so I cannot test this! But I think I have the syntaxes about right. :eek:
 
Upvote 0
Thanks it worked. However, it runs longer....but the end result is perfect.

thanks again for your time and advice.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Update Large Pivot Filter
Replies
5
Views
656
BenW71
B
M
  • Solved
Loop through unfiltered Pivot Table items
Replies
1
Views
220
mikeymay
M
D
  • Question
VBA code to filter out blank values in a pivot table
Replies
2
Views
320
dragontbone
D
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
543
howard
H
G
  • Question
VBA Filter Pivot Table Question
Replies
0
Views
427
gmazza76
G

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

Share this page

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
Back
Top