Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Automating Pivot Tables

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Code:
    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

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

    regards

    Frank

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •