Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Associated data (2000/2003)

    Hello All, I have attached a workbook with an example of my problem. If I select an employee name from the drop down, I need to list all other employees that are in the same location as the employee selected in the drop down, I need the employees names to be listed in the "P" column on sheet1. The emplyees and locations are listed on the employees sheet.
    What would be my best approach to do this.
    Thanks in advance.
    Attached Files Attached Files

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

    Re: Extract Associated data (2000/2003)

    You also shaded part of column Q. Do you need that to be populated too? If so, with what?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    My mistake -I don't need that-thanks

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

    Re: Extract Associated data (2000/2003)

    See attached version, it uses the Worksheet_Change event to run Advanced Filter with the Copy to another location option each time you select an employee in cell A3.
    To do this, I changed the column header in column P to match that in column A, and set up a criteria range in G1:G2 on the Employees sheet.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    This does what I need. On my actual worksheet I'm nt sure how to insert the code. Do I have to make a special module? What is BLAD1?
    Attached Images Attached Images

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

    Re: Extract Associated data (2000/2003)

    To create the code, right-click the worksheet tab of Sheet1, and select View Code from the popup menu. This will open the worksheet module, and that's where you create worksheet event code.

    Blad1 is the Dutch name for Sheet1. Apparently Excel changed the code name for the sheet when I saved the workbook in my Dutch language version. You don't have to do anything with it, you can stil use Sheet1 and Employees.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    OK, I hate to be a nuisance. When I right Click on sheet1 and click view code, there is another macro that is in my original worksheet. This should be a blank area, correct? How do I get a blank page?

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

    Re: Extract Associated data (2000/2003)

    Apparently you already had code there. That need not be a problem. What was the name of the procedure?

  9. #9
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    Sub SaveFile() is the code that comes up in my original workbook.

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

    Re: Extract Associated data (2000/2003)

    And what is the title (caption) of the module window?

  11. #11
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Images Attached Images

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

    Re: Extract Associated data (2000/2003)

    Thanks, but that is not what I asked. I'd like to know the window title. If the module is maximized, the title will be in the title bar of the Visual Basic Editor, otherwise it will be in the title bar of the module window.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    Sorry.
    2007 Incident Assistant.xls-[sheet1 (Code)]

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

    Re: Extract Associated data (2000/2003)

    OK, that's what I wanted to know. Unless that module contains other code already, the SaveFile macro should not be in there, but in a standard module, i.e. the kind of module you create by selecting Insert | Module in the Visual Basic Editor. The worksheet module should only be used for event procedures such as Worksheet_Change.

  15. #15
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Associated data (2000/2003)

    OK, I deleted it and copied it to a module. Then inserted your code. Do I need to do anything else, or should the event change work automatically? I set up everything else as you instructed, but it doesn't print out on sheet 1
    Thanks for your patience and concern with my problem

Page 1 of 3 123 LastLast

Posting Permissions

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