+ Reply to Thread
Results 1 to 20 of 20

Automatically copy row of data from Master Sheet to other sheet based on column criteria

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Automatically copy row of data from Master Sheet to other sheet based on column criteria

    Hello!

    I thought I could do this with normal formulas but I'm having a hard time getting it to consistently perform the way I need it to.

    I have a workbook that contains a few sheets.
    The primary sheet is the "Master Sheet."
    When I put a row into the Master Sheet I would like for it to automatically to go into certain sheets. If Column "B" contains "New" I would like for it to be copied to the sheet titled "New" and the same for "Used".
    I would also like for that same row to be copied into a specific sheet depending on what is entered into Column "E." Column E will be the names of salespeople and they will each have their own specific sheets.
    So every row would ideally be copied into at least two different sheets.
    The complicated part that could be worked around on my end is when I might have to enter two different names in Column E.

    I've attached a working sample sheet.

    I would greatly appreciate any feedback.
    Attached Files Attached Files
    Last edited by Tiller332; 10-02-2017 at 12:54 PM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Do you always enter data into column B and column E or do you sometimes enter data in only one of the columns? I noticed that some of the 'name' sheets have a decimal number in column A. Where does this number come from? As well, all the 'name' sheets don't have headers in row 1. Could you please attach a copy of your file that has headers in all the 'name' sheets otherwise there is no way of knowing what data you want copied from the "Master Sheet".
    Last edited by Mumps1; 10-02-2017 at 12:46 PM.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    I just attempted to upload the updated sheet. I apologize for not having that in there in the first place.
    The decimal numbers in some of the sheets were to indicate when two people were listed in Column E on the Master Sheet.
    For my purposes, every column could be copied into the appropriate sheet.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    OK. I would still need to know if you always enter data into column B and column E or if you sometimes enter data in only one of the columns and not the other before you want that row copied to the appropriate sheet. Also, I have to go out for an appointment now so I will respond as soon as I get a chance.

  5. #5
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Hi Tiller332.

    Welcome to the Forum.

    You could achieve this using Pivot Tables.
    All the various Sheets would be automatically populated.
    Are you "comfortable" with Pivot Tables ?

    Regards

    peterrc

  6. #6
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Quote Originally Posted by Mumps1 View Post
    OK. I would still need to know if you always enter data into column B and column E or if you sometimes enter data in only one of the columns and not the other before you want that row copied to the appropriate sheet. Also, I have to go out for an appointment now so I will respond as soon as I get a chance.
    I apologize for not answering that. I thought I had. I appreciate your help.

    Columns A thru H will always have Data in them. Those will have data entered at the same time. The remainder of the columns may not have data entered in them but it won't hurt if that entire row, potentially empty cells included, were copied to the appropriate sheet.

  7. #7
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Quote Originally Posted by peterrc View Post
    Hi Tiller332.

    Welcome to the Forum.

    You could achieve this using Pivot Tables.
    All the various Sheets would be automatically populated.
    Are you "comfortable" with Pivot Tables ?

    Regards

    peterrc
    Thank you for your response!
    I thought I had an understanding of what they did... I am definitely not comfortable with making them.
    I will take a look into them to see if I can figure it out.
    Any favorite Pivot Table resources you could share?
    Last edited by Tiller332; 10-02-2017 at 01:49 PM.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Try this in the MASTER SHEET module:

    Please Login or Register  to view this content.
    Last edited by xladept; 10-02-2017 at 03:13 PM. Reason: New Trigger - fractionals
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  9. #9
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Hi Tiller332,

    Check out the following link.
    https://exceljet.net/plc/why-pivot-tables
    This will give you a pretty good idea of how "easy" and powerful Pivot Tables are.
    Let me know what you think.
    In the meantime I will "convert" your file to Pivot Tables and upload a copy shortly.

    Regards

    peterrc

    EDIT

    Converted file attached.
    You only add Data to the Master Sheet and all the other Sheets are Pivot Tables and will include the new Dat when refreshed. This can be done automatically via VBA (on sheet activation).
    All the Sales Names Pivot Tables were "automatically" created by Excel.
    I have not icluded the digital numbers (1.5 3.5 etc) as I wasn't sure where these figures came from.
    Let me know what you think.
    Attached Files Attached Files
    Last edited by peterrc; 10-03-2017 at 06:44 AM.

  10. #10
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Wow! Thank you so much for that!

    I was definitely on the wrong page about the capability of Pivot Tables.

    The decimal numbers on the individual pages were to indicate when another salesperson was involved with the deal (when there were 2 names in the "Salesperson" column on the Master Sheet).
    Will the pivot table work properly if I added a "2nd Salesperson" column so that that row would be copied to the appropriate sheets of both Salespeople?
    If so, I could then not copy the "No." column from the Master Sheet and just have a new "No." Column for each individual sheet so that each sheet could have its own proper sub count.

    Does all of that sound like it would still work with the Pivot Table functionality?

    Again, Thank you soo much for your assistance!

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Did you try my solution - what happened?

  12. #12
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Hi Tiller332,

    I don't forsee any problems in "sharing" the sale so the sale (including value)appears on both of the salesmen's sheets,
    However, the total sales values on each of sheets would then be incorrect, i.e. duplicated.
    Is the sale (value/commision?) "shared equally?
    Plese advise.

    Regards

    peterrc

  13. #13
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Quote Originally Posted by xladept View Post
    Did you try my solution - what happened?
    I did try the code in the module. I was unable to get a macro or function to run from it? I'll put that on end-user error though... I do appreciate the follow up.

  14. #14
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Quote Originally Posted by peterrc View Post
    Hi Tiller332,

    I don't forsee any problems in "sharing" the sale so the sale (including value)appears on both of the salesmen's sheets,
    However, the total sales values on each of sheets would then be incorrect, i.e. duplicated.
    Is the sale (value/commision?) "shared equally?
    Plese advise.

    Regards

    peterrc
    The values would ideally be split if there were two names associated with it.
    Could that be fixed by adding, yet another column, labeled "Split," to the master sheet that had a simple Yes or No answer on each row so that in each individual statement an if/then function could be used to automatically split the numbers before displaying? Or is that over complicating it?

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    I just tested it again - seems to be working nicely:
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Hi Tiller332,

    Please see atached "Version 2" of the Pivot Table that I attached in #post 9.
    Does this work for you?

    Regards

    peterrc
    Attached Files Attached Files

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Hi Tiller,
    In the VBE click on Tools-References and choose
    "Microsoft Scripting Runtime" then that should let the program work

  18. #18
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Quote Originally Posted by peterrc View Post
    Hi Tiller332,

    Please see atached "Version 2" of the Pivot Table that I attached in #post 9.
    Does this work for you?

    Regards

    peterrc
    I do like this one! This helps very much!

    I can't tell you how much I appreciate this assistance.

    I do have one question about the pivot tables. When I was playing with this on my own, I was trying to find a way for the filters on the individual sheets to work as "OR" instead of "AND." Is there a way do this?
    Example: I had made two Columns on the Master Sheet, Salesperson & Salesperson2. When on the individual tables I tried to put both of those columns in the filters so that if a name (BARRRY for example) showed up in either column("OR) it would also show in that sheet. When I was trying to work with it, it would only seem to work as an "AND" filter. It wouldn't show anything unless that name was in Salesperson & Salesperson2.

  19. #19
    Registered User
    Join Date
    10-02-2017
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Quote Originally Posted by xladept View Post
    Hi Tiller,
    In the VBE click on Tools-References and choose
    "Microsoft Scripting Runtime" then that should let the program work
    I'm still getting a runtime error.
    I am putting in a row of data on the master sheet, then running the "copyfromdata" macro. I get a runtime error (#13) and it takes me to this line: If Target.Value <> "" Then when I debug it.
    I'm quite positive that this is end-user error and I sincerely appreciate your assistance with this.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatically copy row of data from Master Sheet to other sheet based on column criter

    Try these in place of the existing:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatically copy rows to another sheet based on criteria, when original sheet is updated
    By NoviceProgrammer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2016, 02:14 AM
  2. Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  3. Replies: 1
    Last Post: 09-15-2014, 01:03 PM
  4. Replies: 1
    Last Post: 11-14-2013, 09:24 PM
  5. Copy entire row from Master sheet based on two criteria
    By melispol44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2013, 08:07 PM
  6. Replies: 11
    Last Post: 05-12-2013, 11:30 AM
  7. [SOLVED] How to copy data from master list sheet to another sheet based on category
    By nazrulfadzli in forum Excel General
    Replies: 2
    Last Post: 05-17-2012, 08:19 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1