Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    12715 NW 19th Manor Coral Springs, Florida 33071-7760
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to select from drop down header in 2007

    I get a daily spreadsheet showing the stats for the entire company. I have been selecting my team by clicking on the dropdown header and unchecking select all. There are close to 1,000 names to go through to get my dozen. I want to create a macro to automate. I will have to save the macro in another sheet as this list is generated new daily. Is there anything out of the ordinary that I will need to look out for?

    Thanks in advance

  2. #2
    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
    Is this a pivot table drop down? For the macro I would create a workbook just for this, add the list of names in a worksheet, and put the macro in this based on opening the other book (the one that changes). If the form of the pivot could change, I would try to make the code generic, perhaps adding some variables with basic info to change at the start of the macro for easier editing.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    How is the daily s/sheet structured?

    Hi
    If it looks like a list created as a database extract with multiple/variable rows per staff member you could make use of the Advanced Filter command.
    You could try setting multiple (12) copies of the name column in your criteria. If this works then ensure that copy each name into a separate row in the criteria region to give you the logical OR.
    Never done this with 12 duplicates of a column name.

    Is there a column containing an entry that designates your group eg a branch or regional name ? If so then I guess autofilter on that column should do the trick.
    If not can you request a change to the database report to indicate your group?

    Can you import the excel data into an access database?

    We really need more info on the data source and the desired output.

    There is more than one way to skin a possum. Coding is not always the ony way.

    G

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Example with Advanced Filter

    Hi
    The accompanying screen shot shows an example of a set up with criiteria and extract regions.

    The criteria region F2:F4 selects rows WHERE Product = Chartreuse Verte OR Ipoh Coffee

    The extract region F7:I7 specifies which columns of data to display of the selected record set.

    Each time you perform the extract the region below F7:I7 would be overwritten.

    There is no need to include repeated column entries for Product in the criteris region. My head was in another space at that time.

    Hope this helps

    G
    Attached Images Attached Images

Tags for this Thread

Posting Permissions

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