Automating Pivot Tables

[Gastank]

Is it possible to programmatically set the selection in a Page Field based on the contents of a named field?
I've tried the following code but the Pivot Opens showing (All) in the page field.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Login = "Bob" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
"Warehouse"
ElseIf Login = "Mary" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
"Operations"
Else
ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
"Logistics"
End If
End Sub

Any help would greatly appreciated

 

[Dave Hawley]

Hi Gastank

This is not a direct answer to your problem but this code is used to change the PageField in ALL pivot tables on ALL sheets to the same setting.

Sub ChangeAllPivotHeaders()
Dim SPgField As String
Dim pPt As PivotTable
Dim wWsht As Worksheet

'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Will change the Page Field on 2 or more Pivot tables
'''''''''''''''''''''''''''''''''''''''''''

 On Error GoTo NoPivot
  Set pPt = ActiveSheet.PivotTables(1)
      SPgField = Pt.PageFields("Gender").CurrentPage
  Set pPt = Nothing

On Error Resume Next
For Each wWsht In ThisWorkbook.Worksheets
  For Each pPt In wWsht.PivotTables
    pPt.PageFields("Gender").CurrentPage = SPgField
  Next pPt
Next wWsht

NoPivot:
 Set pPt = Nothing
End Sub

 

[Gastank]

Dave,

many thanks for the reply, I'll give it a try

regards

Frank

 

[Cosmos75]

ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
"Logistics"


Why doesn't this work? It's what you get when you record a macro to change the page field?