+ Reply to Thread
Results 1 to 10 of 10

Copy rows until empty row reached and paste in new worksheet

  1. #1
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Copy rows until empty row reached and paste in new worksheet

    Hello,

    I need some help with a macro to achieve the following:

    I have two worksheets "unmatched" and "matched".

    The unmatched sheet is where all the work is done, once matches are found In the "unmatched sheet" I need an automatic process that will take those matches out of "unmatched" and placed them into "matched"

    I've included a full example in the attached spreadsheet, should not take more then 5 mins for someone with the slightest of knowledge of excel programming. (i can do recorded macros, but it wont work in this case as the record macro will only copy and paste the amount of rows when the macro was recorded and wont do it dynamically)


    Any questions please let me know!

    Many thanks for your help!
    Attached Files Attached Files
    Last edited by ebbo; 02-03-2010 at 06:56 AM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Copy rows until empty row reached and paste in new worksheet

    Hi
    please try this and see if its what you want.
    Attached Files Attached Files
    Last edited by Marcol; 02-03-2010 at 02:21 PM. Reason: Wrong Attachment

  3. #3
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Copy rows until empty row reached and paste in new worksheet

    Marcol I am getting an error with your code. Did you see the example file that I have attached?
    Attached Images Attached Images

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Copy rows until empty row reached and paste in new worksheet

    I have posted the wrong attachment.

    I have edited Post #2 because the file I posted was a test file for another thread.

    Sorry about that.

    Please look again at Post #2.

  5. #5
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Copy rows until empty row reached and paste in new worksheet

    Hello Marcol,

    Thanks very much for this. It works well! Now for maybe some extra bits - which parts of the code do I need to modify so that:

    1) I only copy across column A and B

    2) I can copy across column A,B, C,D,E,F,G........etc (as many as I want)

    Please advise?

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Copy rows until empty row reached and paste in new worksheet

    Hi ebbo

    In
    Sheet1(Unmatched)
    Private Sub CommandButton1_Click()

    This line calls the sub that sorts the data, it can be used for any table you wish.
    Please Login or Register  to view this content.
    "Unmatched" is the name of the sheet where the data to be sorted is
    "ID" is the name of the first column in your table, in your case this is the Primary Column.
    "bla bla" is the name of the last column in your table change this to suit your needs

    P.S. Any or all off the above names can be changed as this is a general sub, just remember the names are case sensitive.

    This sub can also be used to define sort Keys but in this case the options are not really required and therefore the optional defaults are used.

    This line defines what has to be transferred
    Please Login or Register  to view this content.
    This line moves your remaining data up to Row 2
    Please Login or Register  to view this content.
    Change this to match your last column required
    ie C becomes B or whatever BOTH LINES MUST MATCH

    1) I only copy across column A and B
    This concerns me a little

    The code is designed to work on a complete table

    If your Table on sheet "Unmatched" has columns A to say G and you only copy A & B the code will do as you ask, but the result will be a corrupted table.

    Data in columns A and B will be moved up and therefore missalign with remaining data in columns C to G. Be wary of this.

    Hope that answers your questions.

    If you are happy with this please mark your post [Solved]
    If not please feel free to ask more.

    P.S ModSortData contains general sort subs and tools you may want to use in other projects.

    Regards
    Alistair
    Last edited by Marcol; 02-04-2010 at 03:35 PM. Reason: P.S. Added for clarity.

  7. #7
    Registered User
    Join Date
    02-08-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Copy rows until empty row reached and paste in new worksheet

    Please Login or Register  to view this content.
    i wnat to repeat this code to create new sheet with next set of data
    Last edited by arlu1201; 03-19-2013 at 03:43 AM.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Copy rows until empty row reached and paste in new worksheet

    Kishan.radha,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

    Also,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  9. #9
    Registered User
    Join Date
    02-08-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Copy rows until empty row reached and paste in new worksheet

    hi ,
    iam unable to run this macro , need help

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Copy rows until empty row reached and paste in new worksheet

    Kishan,

    I have already told you - please read post 8.

+ 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