+ Reply to Thread
Results 1 to 18 of 18

Copy rows based on cell content

  1. #1
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Copy rows based on cell content

    I have a master sheet with information that I need to have automatically transfer over to the proper sheets based on the content of 2 cells in the row: agent assigned, and status. if the status is "closed" or "4" then I need the row transferred over to the sheet titled "closed production." On top of this, I need some of the information transferred to the proper agent's worksheet based on the agent assigned. I am completely inexperienced in VBA and I have never used a macro for something like this. Please help! I have attached an example workbook. Thanks!
    Attached Files Attached Files
    Last edited by ashncg; 02-14-2011 at 11:19 AM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Hi ashncg
    one possible way would be with the Lead Input sheet "Worksheet_Change"
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    I have never used VBA codes, I don't even know how to go about entering that, but I will try. Thanks!

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Copy the code
    Select the worksheet in which you the code to run
    Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
    Where the cursor is flashing, choose Edit | Paste
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Thank you so much. If anyone can, I'd like to tweak the code so that it copies the entire rows, I'm going to format all of the worksheets the exact same way, by that I mean they will all be identical to the first sheet in the workbook, and then I will simply hide the columns that I don't need on the other sheets.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    It does copy the entire row

  7. #7
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    For some reason, only the first 4 or 5 colums in the row would transfer over with their content.

  8. #8
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    For some reason, only the first 4 or 5 columns would transfer over, I'd enter text on the master sheet but the cells stopped copying after the 4th or 5th column

  9. #9
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    If anyone could please tweak the code so that it continues to copy the cell content from the first worksheet, I'll be finished with this. I have changed the workbook so that all the sheets have the same format as the first one titled "lead input".

    It doesn't copy anything beyond the F column onto the other sheets, I need it to copy all the way through the V column. Then, I will add other columns to some of the sheets past the V column, but those won't need any cell content to transfer over.

    I would really appreciate it, thank you to everyone who's helped out.

  10. #10
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Hi ashncg,
    Can you attach the workbook?

  11. #11
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    I've attached a sample workbook. The code transfers info to other sheets only when the status is equal to 4 (Column F). It also doesn't transfer any columns beyond column F. Just to restate what I need to achieve, I need the entire rows to transfer to the proper agent sheet (based on the content of column C) regardless of whether the status in column F is 4/closed or not. However, If the status is closed, then I need the entire row transfered not only to the proper agent sheet, but also to the last sheet in the workbook, titled "closed1"

    If you need any more info, let me know, and thanks for your interest in helping me figure this out.
    Attached Files Attached Files

  12. #12
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Hi ashncg

    This time i tested
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    That code still only copies the row if the status (F Column) is equal to "4".
    But it does copy the entire row, like I needed. But I still need each row to be copied to the proper agent sheet (Column C) regardless of whether or not the status in column F is equal to 4.

    For example:

    If the column c has the content "2" and column f has the content "3" I need that entire row transferred to the sheet ej1.

    If the column c has the content "2" and column f has the content "4" I need that entire row transferred to the sheet ej1 AND closed1.

  14. #14
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    In the C column (agent assigned)

    1=greg1
    2=ej1
    3=joey1
    4=tom1
    5=jeff1

  15. #15
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    And now for some reason, once again, that code is stopping the copying of rows at the F column.

  16. #16
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Hi ashncg
    reposition the code and removed the entire row
    Please Login or Register  to view this content.

  17. #17
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: macro or vba code needed for copying rows based on cell content, Please Help!

    Hi

    had to add another column with list in w to tiigger the change event
    at the right time to capture the whole completed row .. not just when you reached the f column
    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    01-31-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: Copy rows based on cell content

    Thank you so much, that works beautifully, you are the best.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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