Current Page and Pivot Tables

Posted by Nicole on April 19, 2001 8:21 AM

How do I set the currentpage of a pivot table? The code below does not work. we continue to get an error "Unable to set default property ..." ActiveSheet.PivotTables("PivotTable1").PivotFields("Report Date").CurrentPage = strDate1
Thanks!

Posted by Dave Hawley on April 20, 2001 3:21 AM

Hi Nicole

Pivot Tables can get very frustrating within VBA! here is some code I recently wrote for a person who wanted to set the PageField of ALL pivot tables within the workbook to be the same.

Sub ChangeAllPivotHeaders()
Dim SPgField As String, SPgField2 As String
'Written by OzGrid Business Applications
'www.ozgrid.com
SPgField1 = Selection
SPgField2 = Selection.Offset(1, 0)
Dim Pt As PivotTable
Dim Wsht As Worksheet
On Error Resume Next
For Each Wsht In ThisWorkbook.Worksheets
For Each Pt In Wsht.PivotTables
Pt.PageFields(1).CurrentPage = SPgField1
Pt.PageFields(2).CurrentPage = SPgField2
Next Pt
Next Wsht

.....so in your case this should work

Sub ChangePageField()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim Pt As PivotTable
Dim strDate1 As String

Set Pt = ActiveSheet.PivotTables(1)

With Pt
.PageFields("Report Date").CurrentPage = strDate1
End With

Set Pt = Nothing

End Sub

Of course you must ensure you have a PageField that is equal to strDate1 that the heading is correct too.

Dave

OzGrid Business Applications