Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dropping down a list with a macro (2000)

    I have built a layout I want to use which mixes a graph and calculations deduced from a pivot table which is on a separate worksheet. I dont see any other way to achieve the result I want. What I now need to do is to mechanise a little print routine which goes:-

    start in deductions worksheet
    go to other (picot table) worksheet (no problem)
    go to cell B11 (which contains pivot table page field with drop down list - no problem)
    drop down the list (problem - see below)
    go down one entry (problem - see below)
    press ok / return (problem - see below)
    go back to first worksheet
    print
    .......... and repeat.

    the problem is that the macro recorder returns the results of the drop down selection at the time the macro was recorded.

    is there any way I can use the recorder direct, or modify the resulting VBA code, to achieve what I want?

    Thanks

    Mike C

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

    Re: Dropping down a list with a macro (2000)

    If you know the values of the page field, you can set the page field without selecting it:

    Worksheets("Sheet1").Range("A3").PivotTable.PivotF ields("Country").CurrentPage = "US"

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dropping down a list with a macro (2000)

    The pivot table list (as is the autofilter pulldown list) are not available to us via VB. And the pivot code is not
    Thus when you record the macro (for either) you only record the results of the macro (putting the second item into the field, not selecting the field, choosing the 2nd item etc).

    If you want a particular item from the list to enter, you will either:
    Have to figure out via code or logic what the second item is. If it is something like the current year or something like that there are other ways to get it. If not you will have to create your own "unique list", sort it, then figure out the second item.

    Here is some code from John Walkenbach that might get you started. It uses a collection to make a unique list from a range, and sorts it. It finishes with putting it into a listbox, but you could just get the 2nd item if that is all you want. and you can feed it into the routine for the pivotfield directly.

    Hope this helps,

    Steve

  4. #4
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropping down a list with a macro (2000)

    Thanks guys,

    So, Hans, if I have an external list of all the values (which I can grab from a pivot table analysis and "paste values" somewhere) I can create a variable and step through your code one by one to mimic a formal print routine.

    Thanks,

    Mike C

  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropping down a list with a macro (2000)

    Thank you - I should have read both replies before sending any!. What you have given me is a way of mechanising the step in my reply to Hans to get a primed list of values from a list with duplicates....

    Mike C

Posting Permissions

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