+ Reply to Thread
Results 1 to 10 of 10

Copy rows from one worksheet to another, where column values are the same

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Copy rows from one worksheet to another, where column values are the same

    Hello VBA Gurus,

    I have this script that will grab the value of a selected cell in column 1 of sheet 2, find the value in a cell of column 1 of sheet 1, then copy that specific row from sheet 1 to sheet 2 in a row inserted beneath the selected cell in column 1 of sheet 2... it will do the same for all subsequent cells in column 1 of sheet 2 until it reaches an empty value.

    The problem I have is that there are some rows with different values that have the same searched cell value... I have been trying to figure out how to modify the code so that it will copy subsequent rows with the same cell value (some cell values have a single row of values, while others may have more..). I have not been successful, and unfortunately only the first row of values is returned for all subsequent cells with the same value -- the same row is copied multiple times instead of all the various rows that have the same cell value in subsequent rows). I would like them to be copied maintaining the original order for a given cell value -- though overall cell values are ordered differently between the two sheets (which is the point of the script -- to order the rows from sheet 1's cell value order (with the original order of rows in a block of rows with the same cell value).

    Did I explain that clearly? Here is what I have so far... any help to incorporate the described features would be greatly appreciated.

    Thanks in advance.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Copy rows from one worksheet to another, where column values are the same

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy rows from one worksheet to another, where column values are the same

    Sample(After).xlsmSample(Before).xlsm

    Hello JapanDave,

    Attached are "Before" and "After" .xlsm files. I've stripped down sheet 2... the top cells have single value (1 for 1) while the lower row cells have multiple row values (all returned with the 1st row instance and none of the subsequent rows with the same cell value are returned).

    I hope this helps make things clearer.

  4. #4
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Copy rows from one worksheet to another, where column values are the same

    Try this, click the arrow.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy rows from one worksheet to another, where column values are the same

    Hello JapanDave,

    That works very well. You have made improvements to the code and execution time. Thank you for your help. I do appreciate it.

    If I did not want to insert a row... and I just wanted to place it in the same row as the matched cell... how would I modify the code to do that.

    I'm a bit of a novice at VBA. I am trying a few things but I am not able to accomplish this.

  6. #6
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy rows from one worksheet to another, where column values are the same

    Quote Originally Posted by klauber View Post
    Hello JapanDave,

    That works very well. You have made improvements to the code and execution time. Thank you for your help. I do appreciate it.

    If I did not want to insert a row... and I just wanted to place it in the same row as the matched cell... how would I modify the code to do that.

    I'm a bit of a novice at VBA. I am trying a few things but I am not able to accomplish this.
    Oh. I figured it out! Thanks again for all your help, JapanDave.

  7. #7
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy rows from one worksheet to another, where column values are the same

    Hello JapanDave,

    I actually found a bug when I used the entire column size.

    There are 21 rows that did not copy over (rows 3808 thru 3828 of sheet 1), they are neglected. These values should be pasted to row 8118 of sheet 2.

    I've been trying to find out why the script cuts off at line 3807 for that particular cell value... but I haven't had any success yet. Might you help me discern why it does not capture all the rows?

    I'm attaching a full version of the cell values with your subroutine.
    Sample(Before)-Full.xlsm

    Thank you.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy rows from one worksheet to another, where column values are the same

    Try changing this line

    Please Login or Register  to view this content.
    INTO
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy rows from one worksheet to another, where column values are the same

    That seems to have done it.

    Much thanks!

  10. #10
    Registered User
    Join Date
    12-11-2012
    Location
    Unites States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy rows from one worksheet to another, where column values are the same

    I have encountered a case where there is an unexpected extra row from the source that is not copied over to the target.

    Specifically, in the attached file... row 266 of sheet 1 does not copy over to sheet 2, row 1 target value. If there are excess rows in sheet 1 that are not accounted for in sheet 2... is there a way to have them inserted for a given target value? Using "row_copier" macro -- module1

    I hope that looking at the example file will enable a better understanding of my problem. Thanks in advance for the help.Sample(Problem).xlsm
    Last edited by klauber; 07-26-2013 at 04:09 PM.

+ 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