Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page field in pivot table (2003)

    I have a work book with several pivot tables (18). Each pivot table has a page field in order to select a country. How can I change the value of each page field in one shot instead to go to each pivot table to change the page field one by one.
    Regards,
    Marcel

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page field in pivot table (2003)

    Say that the page field for the first pivot table is in F1, and that for the next one in K1. You could create a Worksheet_Change event procedure for the worksheet containing the pivot tables:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F1")) Is Nothing Then
    Application.EnableEvents = False
    Range("K1") = Range("F1")
    ' Repeat for other page field cells
    Application.EnableEvents = True
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page field in pivot table (2003)

    Hello Hans,

    I did it and it was working fine, then I did some tests by hidding and protecting some cells. I deleted all the changes done with the tests and now it doesn't work anymore. It seems that the Worksheet_Event is not triggered any more (I have put a breaking point at the beginning of the code).
    What is wrong?
    Thanks and regards,
    Marcel

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page field in pivot table (2003)

    Make sure that the code is in the module belonging to the worksheet containing the pivot tables, and that it looks like the screenshot below. If the left dropdown says (General), there is probably a typo in the code.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page field in pivot table (2003)

    The code was right, I had to close Excel and re-open it.
    Thanks and regards,
    Marcel

Posting Permissions

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