Selecting Pivot Table Data on Refresh

[SherryO]

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]

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]

Unfortunately, that only works for data items and this is a column item. Thanks anyway.

[geekgirlau]

You can do it with code:

[vba]
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
[/vba]