+ Reply to Thread
Results 1 to 3 of 3

Selecting next drop down item in pivot table with a macro ???

  1. #1
    Keilan Knight
    Guest

    Selecting next drop down item in pivot table with a macro ???

    Hi there!

    Wondering if anyone can help me with this problem. I am fairly new to
    macros and haven't even come across pivot tables until now, so any
    help would be appreciated here.

    What I have is a spreadsheet which links into an OLAP cube to run
    filtered reports, there is a date drop down list which is the main
    filter, this goes like this...

    Time By Week
    |
    +-Year
    +------Week
    +-----------Day
    +---------------Shift

    This is filtered usually by week (we don't drill down as low as day or
    shift, but the option is there should anyone need it). Now, the
    problem is, that every week the managers link to this sheet via the
    intranet to see the latest figures, and someone has the unthankful
    task of opening 100 spreadsheets, advancing to the next week, then
    resaving the sheet, to save the managers some time! Now, I have worked
    out a VBS script which can open all these sheets, run a macro and then
    close the sheets, and I can schedule this script weekly, what I don't
    know is how to , in VBA, change the time by week drop down list to the
    next week. I have tried recording a macro and manually changing it,
    but this just shows me how to set it to a known value. Is there a way
    i can dynamically change it to just the next record? For what its
    worth, here is what I get when I manually change it to the next record
    in Macro Recorder....




    ActiveSheet.PivotTables("PivotTable2").PivotFields("[Time By Week]").
    _
    CurrentPageName = "[Time By Week].[All Time By
    Week].[2005].[Week 6]"


    Anyone able to help? Thanks!!!!!!

  2. #2
    Tom Ogilvy
    Guest

    Re: Selecting next drop down item in pivot table with a macro ???

    num = 6
    ActiveSheet.PivotTables("PivotTable2"). _
    PivotFields("[Time By Week]"). _
    CurrentPageName = _
    "[Time By Week].[All Time By Week].[2005].[Week " & _
    num & "]"

    Would be my guess. Not sure how the two digit weeks look, but if they are

    Week 11
    then it shouldn't be a problem.

    If they are Week11 then you would need a slight modification.

    num = 6
    snum = Right(" " & num,2)
    ActiveSheet.PivotTables("PivotTable2"). _
    PivotFields("[Time By Week]"). _
    CurrentPageName = _
    "[Time By Week].[All Time By Week].[2005].[Week" & _
    snum & "]"




    --
    Regards,
    Tom Ogilvy

    "Keilan Knight" <keilan.knight@gmail.com> wrote in message
    news:ab3678c2.0502210408.218e0dc9@posting.google.com...
    > Hi there!
    >
    > Wondering if anyone can help me with this problem. I am fairly new to
    > macros and haven't even come across pivot tables until now, so any
    > help would be appreciated here.
    >
    > What I have is a spreadsheet which links into an OLAP cube to run
    > filtered reports, there is a date drop down list which is the main
    > filter, this goes like this...
    >
    > Time By Week
    > |
    > +-Year
    > +------Week
    > +-----------Day
    > +---------------Shift
    >
    > This is filtered usually by week (we don't drill down as low as day or
    > shift, but the option is there should anyone need it). Now, the
    > problem is, that every week the managers link to this sheet via the
    > intranet to see the latest figures, and someone has the unthankful
    > task of opening 100 spreadsheets, advancing to the next week, then
    > resaving the sheet, to save the managers some time! Now, I have worked
    > out a VBS script which can open all these sheets, run a macro and then
    > close the sheets, and I can schedule this script weekly, what I don't
    > know is how to , in VBA, change the time by week drop down list to the
    > next week. I have tried recording a macro and manually changing it,
    > but this just shows me how to set it to a known value. Is there a way
    > i can dynamically change it to just the next record? For what its
    > worth, here is what I get when I manually change it to the next record
    > in Macro Recorder....
    >
    >
    >
    >
    > ActiveSheet.PivotTables("PivotTable2").PivotFields("[Time By Week]").
    > _
    > CurrentPageName = "[Time By Week].[All Time By
    > Week].[2005].[Week 6]"
    >
    >
    > Anyone able to help? Thanks!!!!!!




  3. #3
    keilan.knight@gmail.com
    Guest

    Re: Selecting next drop down item in pivot table with a macro ???

    Thanks for the reply Tom!

    I think the best thing I can take out of that is probably the simplest
    thing, escaping to a variable within the page name using & var & , I
    haven't used this language so did not know how to include variables
    (had tried all variations of methods I knew!

    What I've done is used primitive excel macros and C&P the drop down
    field into another cell, which copies it as just a general cell, I've
    then copied the cell to the cell below, which has incremented it's
    value from Week 1 to Week 2 , I then create a variable varWeek from
    that new cell, and set the current page name to

    CurrentPageName = "[Time By Week].[All Time By Week].[2005]. _
    ["& varWeek & "]"

    Works a treat! Thankfully this works well too...

    IF varWeek > "Week 52"
    THEN varWeek = "Week 1"
    ENDIF

    I've also replace the year with a variable, with I get with the
    following

    varYear = Trim(VBA.Format(Now(), "YYYY"))

    Hoepfully this means, when the macro runs in the 1st week of 2006, it
    will select 2006 as the year and reset Week to Week 1. Thanks for
    helping me in simplifying this for me!!

    Regards,
    Keilan


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1