Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controlling Pivot Tables from VBA (2000 (9.0.4402 SR-1))

    I have a problem trying to automatically set the page field on a pivot table. When the pivot table is created and a field such as "Posted Date" is placed into the page section, the pivot field defaults to "All". What i have been trying to do, with limited success, is once new data has been imported and the pivot table refreshed, is to change the page field from ("All") to display the posted date. I.e a variable called "posted" is set to the posted date and with the following code I was hoping it would set the page field: -

    "ActiveSheet.PivotTables("fi000029Pivot").PivotFie lds("Post Date").CurrentPage = posted"

    I've tried setting the variable to both date and string, but the app seems to just skip over the statement. I have set the code to say ("All") and ran it when I've manually chosen the date and that works!

    Can someone guide as to where I'm going wrong, please?

    Cheers,

    Niven

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

    Re: Controlling Pivot Tables from VBA (2000 (9.0.4402 SR-1))

    Try a formatted date string:

    ActiveSheet.PivotTables("fi000029Pivot").PivotFiel ds("Post Date").CurrentPage = Format(posted, "dd/mm")

    or whatever format is appropriate.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Pivot Tables from VBA (2000 (9.0.4402 SR-1))

    Many thanks for that. This has been very temperamental. It didn't originally work for the app in question, but did when I recreated it!

    I'm a bit puzzled by this and will be keeping a close watch when I have to use it again.

    Cheers,

    Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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