Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    I have a spreadsheet with all our employees and their job titles. I want to copy/extract the employees with the job title counsel, partner and associate onto a new spreadsheet (in the same workbook). I would also like this to automatically update if I add or delete an employee. Can anyone help me?

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

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    Welcome to Woody's Lounge!

    Why would you want to create an exact linked copy of a worksheet?

  3. #3
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    My manager wants to open the workbook and see a list of all employees and job titles (etc.). But she also wants to be able to quickly see a list of just the associates, counsel and partners. I can't use autofilter because you can only do 2 criteria, plus she doesn't want to do any work herself, she just wants it there in front of her. Does that make sense? So the second spreadsheet would just need to display the rows that matched certain criteria (ie. contained the words, associate, counsel or partners).

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

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    I'd still use AutoFilter. You can add an extra column. Say that Job Title is column B, with data starting in row 2 (row 1 is the column header).
    In row 2 in the extra column, enter the formula

    =OR(B2="Associate",B2="Counsel",B2="Partner")

    and fill down as far as needed.
    To select only employees whose Job Title is Associate, Counsel or Partner, select TRUE in the autofilter dropdown for this column.
    To make it easier, you can put a command button in row 1 that toggles the filter on and off. If you do this, you can hide the extra column.
    See the attached very simple demo. (Download the workbook to your hard disk, then enable macros when you open it.)

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    When I read the part where the senior partner for life wanted the data to just appear without he/she doing anything <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> , an OR statement that might need some editing didn't seem like an option.

    It also didn't dawn on me that AutoFilters (even through automation) were limited to only two choices.

    This seemed like a job for a UserForm (kindly forgive the lack of aesthetic beauty). I call it Smedley. It's ugly but it works. If you check out the Form_Initialize code, you may add more job titles if required. You'll need to Enable Macros, and the code assumes the list is in the first Worksheet, and the list starts in row 3....and it prefers that you call it Smedley.

  6. #6
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    I think this will work. Can you just tell me how to create the command button.

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

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    Select View | Toolbars | Forms, or right-click any toolbar and select Forms from the popup menu.
    Click the Command Button button on this toolbar, then click in the worksheet, or drag a rectangle on the worksheet.
    Excel will ask you to assign a macro to the button.
    If you have already created one, select it in the list, then click OK.
    If you haven't created the macro yet, enter a name in the box, then click New. This will take you to the Visual Basic Editor.
    You can also cancel the Assign Macro dialog now, and assign a macro at a later moment.
    If you click in the worksheet outside the button, you deselect it. You can now click it (whether it does anything depends on whether you assigned a macro).
    If you want to modify it, right-click it - you can format the button, edit its caption and assign a macro. While it is selected, you can also move and resize it.

  8. #8
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    Thanks. When I right-click on the command button, I do not get a Assign Macro menu. I looked up instructions in the Word Help as well and it said I should. Any ideas?

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

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    Word? We're talking about Excel here. Are you sure you created a command button from the Forms toolbar? What items do you see in the right-click menu?

  10. #10
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    Sorry about that. I meant Excel and I just figured out my mistake - I used the Control Box toolbar not the Forms toolbar. Now it is working perfectly. Thanks for all your help. It looks great and I learn a lot!

  11. #11
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    My manager loves the button but asked me "Is it possible for the button to change colour when the option is selected?" Any ideas?

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

    Re: Copy/Extract Info from one Spreadsheet to Another (Excel 97)

    The color of the command button from the Forms toolbar is determined by the user's Windows color scheme (set in the Appearance tab of the Display Properties control panel). You can use the toggle button from the Control Toolbox instead.

    If you place a control from the Control Toolbox on your worksheet, you automatically turn on design mode.
    You can create code for the control by double clicking it. This code is not placed in a standard module, but in the worksheet module.
    You can format the control by right-clicking it and selecting Properties from the popup menu.
    When done, turn off design mode by clicking the first button on the Control Toolbox.

    See the attached version.

Posting Permissions

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