Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Moving data from one sheet to another based on a "key"

    This has always driven me nuts (nuttier than I am?).

    Given a master sheet with column headings, I want move row data to various other sheets based on an entry in, say, column D. For example, column D might have names (duplicated numerous times). I want to move a set of columns to the "KEVIN" sheet whenever KEVIN appears in col. D.

    Of course, without out moving them, I could filter the data based on the name column.

    I could also record a macro that, after filtering the data, copies what I want then moves to another sheet (but I don't know how to move the THAT specific sheet in the macro) and paste the data. If this is the best way (??), how can the macro identify which sheet tab?

    Is there another way to have this happen to all the sheets automatically when a new row is entered -- i.e., move the selected data from that row, based on the name, to the last row (it now a new row) in the named sheet tab?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    What you want is an Advanced Filter. This will allow you to make the selection based on a Criteria and then copy the selected rows to the Kevin sheet. Attached is an excerpt from a course I used to teach. Be aware that this is Excel 2003 but the concepts are the same only the menu/Ribbon choices are different in newer versions. HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Yes, RG, that'll work. But, it's laborious and has to be done over and over again.

    What I'd like to happen is that whenever a new row is placed on the master sheet, that row will then be automatically placed in the respective "name" sheet as the then new (last) row. Maybe this can't be done automatically.

    The way I'm currently handling this is to have IF statements check on each sheet for the name and then insert the data. I apply this to several hundred rows and then filter each named tab. I just thought there MUST be a cleaner way.

  4. #4
    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
    Instead of entering into the MASTER sheet, why not just enter into the Kevin sheet. If all the entries are moved, what is in the Master sheet anyway? Why not have one MASTER sheet with ALL the entries instead of "Master" with some entries and other sheets with some other entries. It just seems to me to be more "messy".

    I recommend the master sheet with all the data, rather than trying to maintain multiple copies: with autofilters, subtotal and a pivot table it is easy to manage and work with the list.

    Automatically doing the move can be done, but you will have to decide when to trigger the move. Will the entire row get moved when "Kevin" is entered into Col D or ill it wait until all entries in the cols are done? [If kevin is put in first, the row will be moved with no other entry...]

    Having a macro that checks for every cell entry, can make the workbook sluggish and disables UNDO (which many people like and depend on).

    If you are going to do something of this sort, it may be better to NOT move it after entry, but instead trigger macros when worksheets are activated. When the Kevin sheet is activated, you could then MOVE all the Kevin entries from the master to the Kevin sheet. This keeps Kevin updated whenever it is used but would not disable the UNDO or lead to sluggish entry (only some possible sluggishness when a new sheet is activated). A simple autofilter on the master and move the results to the end of the kevin sheet is doable. If there are few sheets to update Worksheet_Activate with separate procedures for each sheet, or if many a generic Workbook_SheetActivate would make more sense

    Steve

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    See if this meets your needs.
    Kevin Move Entries.xlsm
    Please note:
    1. There is a control code in the MasterSheet (sheet1).
    2. The main code in in Module1
    3. This code assumes that the name is the last item to be entered before the copy
    4. Also I didn't include the Application.DisplayAlerts=False statement in the code of Copyit which you may want to do to prevent flashing of the screen turn it back off with DisplayAlerts=True at the end of the Copyit code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That does the trick, THANKS.

    Explain something in the VB, please.

    When a new row is entered in the Master sheet, is that row the ONLY one copied to the respective sheet or is the code re-copying all of the rows from Master. I suspect the former, but not sure where that happens in the code.

    I also noticed that if a name gets changed in the master, the respective sheet tab gets updated.
    But, if the name is changed back or changed to another, that sheet gets changed but the first one remains.
    Hmmm. Maybe that answers my previous question. Only the row where the name cell changes is addressed. And, no removal takes place if the name was changed.


    If I change a row with Bob to Joan, Joan gets the new row...which remains there even if I change Joan to Kevin or even back to Bob.

    Would it be awful to have a macro to redo the entire set of tabs based on the Master?
    Last edited by kweaver; 2013-07-17 at 13:06.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    You are correct in your observations. Code could be written to do the other functions but that gets a bit tricky since you have to match up enough cells to make sure you are deleting the correct entry from the correct sheet which would include saving the existing name prior to the change. Complicated but do able.
    This is where setting up the Advanced Filter in VBA code would be most useful.
    Only the row where the name cell changes is addressed.
    That is what the Intersect code does.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Would the code be fairly simple to remove all of the info (except the title row) in the tabs and regenerate it from the master?

    Also, could that be a grinder if there were, say, 100 rows that have to be regenerated?
    Last edited by kweaver; 2013-07-17 at 13:18.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Ok, here's version 2.

    Note: Nothing happens until you press the Generate Extracts button!
    If you enter a name that doesn't have a sheet you will get a message to correct the situation.
    Pay attention to the Setup of the named sheets (header row only) and also the naming convention for those headers.
    This also uses a dynamic range name for the Master list you can check it out in the Names function and adjust as necessary.

    HTH

    Kevin Move Entries V2.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Steve, I suggested to the user to simply keep the master sheet and filter it. But, no! Didn't want that. Oh well.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG, I don't think v2 gives an error message if there's a missing sheet for a name.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I moved the macro to a module in my file. Also created the same MasterList range and extended the range to 12 columns (all of the required data). My name criteria is in column 8.
    But, got an error here:

    Range("MasterList").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("Criteria"), CopyToRange:=Range("Extract" & zSheetName), Unique:=False

    Do I need to create all of the range names?
    Last edited by kweaver; 2013-07-17 at 16:30.

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Yes each of the "Named" sheets needs to have it's Headers (row1) highlighted and the range named "Extract"+sheetname.
    Make sure you use the proper method of setting up those headers, i.e. using Absolute References to the Master sheet as in my example. This ensures that if you change a header title in the master it will change in all of the named sheets.

    Don't forget to make sure the Criteria sheet has its header changed to the 8th column! HTH

    BTW: If you have a really large number of NAMED sheets the naming of the ranges could be automated.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Pardon me, but isn't this the same thing as per:
    Large data set filtered to new tabs
    Started by Larry P, 2013-07-11 12:21

    zeddy

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Creating all those named ranges would need to be automated somehow; otherwise, if filtering and creating them all needs to be done, the user might as well just copy and paste (I think).

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
  •