Visible Property

You can hide items by setting their Visible property to False. Say you are working with the grouped dates from the last exercise, and you want to see only Jan 2006 and Jan 2007, as shown in Figure 7-12.

Pi /ottabtsijisrn

n

t

A

a

C □ E

F G

H

I

J

1

1 2

3

Sum of NumberScld Column Labels *

4

Row Labels

I^NSW

NT QLD SA

TAS VIC

YJA

Grand Total

5

sum

6

Jan

17B4

673 1779 879

1144 2120

936

9315

7

- 2007

8

Jan

1244

1631 672 3188

1739 1261

780

10515

9

Grand Total

3028

2304 2451 4067

2883 3381

1716

19830

10

11

10

-

H

► M Sheet3

Sheets Sheetl Sales. -.j '

m

You could use the following code:

Sub CompareMonths() Dim pvt As PivotTable Dim pvi As Pivotltem Dim sMonth As String

'Specify month to be visible sMonth = "Jan"

'Get reference to PivotTable

Set pvt = ActiveSheet.PivotTables(l)

'Hide all years except 2006 & 2007

For Each pvi In pvt.PivotFields("Years").PivotItems If pvi.Name <> "2006" And pvi.Name <> "2007" Then pvi.Visible = False End If Next pvi

'Make sure specified month is visible - can't hide all data pvt.PivotFields("Date").PivotItems(sMonth).Visible = True

'Hide all months in Date except specified month For Each pvi In pvt.PivotFields("Date").PivotItems

If pvi.Name <> sMonth Then pvi.Visible = False Next pvi

End Sub

CompareMonths loops through all the items in the Years and Date fields, setting the Visible property to False if the item is not one of the required items. The code has been designed to be reusable for comparing other months by assigning new values to sMonth. Note that the required month is made visible before processing the items in the Date field. This is necessary to ensure that the required month is visible, and also so you don't try to make all the items hidden at once, which would cause a run-time error.

0 0

Post a comment