1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - automatic copying of rows between sheets

Discussion in 'Business Applications' started by boysha8, Jul 17, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. boysha8

    boysha8 Thread Starter

    Joined:
    Jul 17, 2007
    Messages:
    3
    I have an excel file with 5 sheets. Sheets 1 to 4 have rows of data, sorted under various columns, out of which one column in each sheet is titled "priority" and every row has 'high', 'medium' or 'low' populated in the priority column. I need to extract all 'high' priority items and automatically copy those entire rows into sheet 5 automatically, either real-time, or by pressing an update button or something in sheet 5.

    Can anyone tell me how it can be done, either through a macro or a simple 'if' 'then' condition.

    Thanks
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    First, you need to decide if you want this done by manual firing or automatically. Second, do you want us to tell you the order of operations for this, or do you want us to create the code for you? You're going to need VBA (macros) for this.

    One thing to think about is putting an additional column in your 5th sheet to identify the sheet. If you do not, you're likely to either delete additional information or have duplicate information. With a column of data for the sheet name (or index number) you can filter the sheet, delete the data and re-copy the data from your 1-4 sheets.

    Right off-hand I would think that an automatic script running like this would slow things down too much. You have many options regarding events which you could fire a script from, such as sheet activation or deactivation. If you do decide to go manually, you may want to think about putting a date/time stamp on this sheet so you have that much more information about when it was updated. Maybe you would want to think about a custom menu or toolbar solution where you could check stats and perform your manual upgrade.

    There are many options to you. Think about what you would want and if you could upload a sample file that would really help. :)
     
  3. boysha8

    boysha8 Thread Starter

    Joined:
    Jul 17, 2007
    Messages:
    3
    hi firefytr, thanks for all that info. i am attaching a sample file here. the rows with priority 'high' in the 4 different tabs are also in the HI PRIORITY tab (i forgot one more detail, the status should not be 'closed' for that row). the HI PRIORITY tab is what should be populated automatically and all manual entry should be in the other 4 tabs only.

    As per your question as to how the action should trigger, it could probably be one of the two

    1. activation of the hi priority sheet should populate the sheet with all the priority 'high' rows, which are not status 'closed'

    or

    2. place a button or link on the hi priority sheet that will populate the sheet with all the 'high' priority rows from the other tabs, at that point of time.

    Let me know which is a better approach and then how to execute that approach

    Thanks
     

    Attached Files:

  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well, the choice is really yours to make. We could also do an action where when an item is marked as HIGH priority and not status CLOSED (i.e. worksheet change event) then that info would be updated to the 5th sheet.

    You also need to identify what you want to do about information which is already transferred. Do you want to copy the row no matter what? Or do you want to copy the row and replace the row if it existed previously? Also, what if the item changes from HIGH to a lower priority? Or if the status is changed from CLOSED? Would you also want to delete the original row after it was transferred? And one last question, will the rows in the CSR, CE, PMA & MP sheets grow? None of them go over 20. Will this need to be dynamic for you (grow/shrink)?
     
  5. boysha8

    boysha8 Thread Starter

    Joined:
    Jul 17, 2007
    Messages:
    3
    Well, lets see if we can do it this way then. Whenever the 'hi priority' sheet gets focus, all items with priority 'High' and status NOT 'Closed' will get populated afresh in that sheet (i.e., on getting focus, all items in the sheet will be deleted and a fresh search will populate that sheet). Copies of the items that get populated will remain in their original sheets. The 'hi priority' sheet will only act as a view, all changes, updations, entries etc will be made in the other 4 sheets.

    The list could grow, so may have to be dynamic. Can go upto 50 in each tab.

    Thanks a ton
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Okay. And so the entries currently in 'hi priority' sheet will then be deleted? If not, what about duplicates?
     
  7. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    What about a pivottable which pulls data from each of the sheet tabs that is setup to only find high priority items?
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/596848

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice