PDA

View Full Version : Selecting Pivot Table Data on Refresh



SherryO
08-01-2006, 06:24 AM
I have a pivot table that I want to set to the last 13 items in a field. It's a text field with dates in it. I also have another pivot table that I would like to have select a project number (also text) to that of a particular cell. Does anyone know how to do that? Thanks!!!

Pkumar
08-01-2006, 10:29 AM
For the first part of ur question right click on the pivot ..select from the drop down "field settings" ...option box will open up click on advance..on the right below u can select the TOP or the BOTTOM fields from the pivots...

Hope this should help !!

SherryO
08-01-2006, 10:52 AM
Unfortunately, that only works for data items and this is a column item. Thanks anyway.

geekgirlau
08-01-2006, 08:55 PM
You can do it with code:


Option Explicit
Sub GetDates()
Dim dtm As Date
Dim pvt As PivotItem
Dim i As Integer


' find minimum date from the top 13
dtm = WorksheetFunction.Max(Range("RawDates"))

For i = 1 To 12
Range("SetDateCrit") = "<" & CLng(dtm)
dtm = WorksheetFunction.DMax(Range("RawDates"), 1, Range("DateCrit"))
Next i

' hide items in the pivot
Sheets("Pivot").PivotTables("pvtTest").RefreshTable

With Sheets("Pivot").PivotTables("pvtTest").PivotFields("Test Date")
' display all items first
For i = 1 To Range("RawDates").Rows.Count - 1
.PivotItems(i).Visible = True
Next i

' hide all dates < top 13
For Each pvt In .PivotItems
pvt.Visible = Not pvt.Value < dtm
Next pvt
End With
End Sub