[RESOLVED] [Excel]: How to reorder ranges in predefined order.-VBForums
Results 1 to 13 of 13

Thread: [RESOLVED] [Excel]: How to reorder ranges in predefined order.

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    6

    Resolved [RESOLVED] [Excel]: How to reorder ranges in predefined order.

    Hi,
    I need some VBA help in copying ranges from one sheet to another and pasting them on a predifined order. I have one worksheet that contains a large number of ranges with data on alphabetical order. Each entry on this sheet contains several rows of data and is identified by a name (e.g. entry "David" = rows 6:11, "Mark" = rows 13:18 and "Rose" = rows 20:25). I need to select certain ranges from this sheet and copy them on a predefined order on a different sheet (e.g 1) Rose, 2) David 3) Mark. The sequence is neither set by date, alphabetical order, nor by value. I have the order already predifined. I guess I could do this by specifiying the source sheet ranges and the destination ranges as well, but as I have over 2000 entries (each consisting of six rows), I need an efficient way to do it. Any help you could provide would be greatly appreciated.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: [Excel]: How to reorder ranges in predefined order.

    as i don't know how you have your predefined order for the named ranges, i will assume some worksheet col A, does not matter anyway just change to suit
    vb Code:
    1. j = 1 ' start row for destination
    2. for i = 1 to sheets("porder").usedrange.rows.count
    3.       sheets("source").range(sheets("porder").cells(i, 1).value).copy sheets("destination").cells(j, 1)
    4.       j = j + 6 ' next row position for copy
    5. next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    6

    Re: [Excel]: How to reorder ranges in predefined order.

    Many thanks for your reply!
    I realise I didn't provide a good explanation to my problem. I am attaching a workbook with some extracts from the main (source) sheet. The predefined order would be the following:

    1. Rose
    2. David
    3. Mark

    4. Patricia
    5. Rani
    6. Paul

    7. Karl
    8. William
    9. Rene

    10. Olivia
    11. Mary
    12. Nancy
    13. Peter

    These ranges would need to be copied into a new sheet according to this predefined order. But as you can see, the order is completely subjective and it's not specified within the source worksheet. I am at a loss as to how to define this order within the code as the workbook in reality contains thousands of records. From the code you kindly offered, should I specify this order within a separate sheet and run the code from there? Again, any help you could provide would be greatly appreciated!
    Attached Files Attached Files

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: [Excel]: How to reorder ranges in predefined order.

    well you need to define the list someplace, separate worksheet, or text file being the logical choices
    does not matter which sheet contains the code, i would be using a module myself, as long as you specify fully qualified ranges, as i did in the sample

    for testing purposes, put a few of those names in a new sheet (six would do for a start), change the sheet names in the code i posted and run it so you can check the results, if it works correctly for 6 should work ok for 6000 (as long as you don't run out of rows)
    Last edited by westconn1; Aug 23rd, 2009 at 06:11 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    6

    Re: [Excel]: How to reorder ranges in predefined order.

    Thanks Pete,
    I tried testing the code with some names from the pre-defined order in a "porder" sheet. But, I keep getting "Run-time error 1004". Is there something I'm doing wrong, you think?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: [Excel]: How to reorder ranges in predefined order.

    on which line?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: [Excel]: How to reorder ranges in predefined order.

    Congrats Pete on your 10000 post

    @mernst : You need to show us the "porder" sheet along with the code that you are using...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  8. #8

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    6

    Re: [Excel]: How to reorder ranges in predefined order.

    On:
    sheets("source").range(sheets("porder").cells(i, 1).value).copy sheets("destination").cells(j, 1)

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: [Excel]: How to reorder ranges in predefined order.

    an empty cell in porder could cause that problem
    does it happen on first row or where?

    thnx kool
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    6

    Re: [Excel]: How to reorder ranges in predefined order.

    It seems to happen on the first row, as the error report stops the procedure immediately after it begins running. I checked the porder sheet and there are no empty rows between cells. Thanks a lot for your help!

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: [Excel]: How to reorder ranges in predefined order.

    i tested again, no error, you sure your 3 sheet names are correct?

    that error will occur if there is no named range to match the name in in porder list

    Each entry on this sheet contains several rows of data and is identified by a name
    from this i assumed that you were using named ranges, but maybe this wais an incorrect assumption
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: [Excel]: How to reorder ranges in predefined order.

    try this method to see if it will work for you, put the code below into the same loop as before
    vb Code:
    1. Set r = Range("a:a").Find(Sheets("porder").Cells(i, 1))  ' assumes the name to match is in column A
    2. If r Is Nothing Then
    3.     MsgBox Sheets("porder").Cells(i, 1) & " not found"
    4.     Else: Set r = Range(r, r.Offset(5, 4))     '6 rows 5 cols change to suit
    5.         r.Copy Sheets("sheet2").Cells(j, 1)
    6.         j = j + 8   ' leaves 2 blank rows between each imported range, change to suit
    7. End If
    Last edited by westconn1; Aug 24th, 2009 at 04:50 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    6

    Re: [Excel]: How to reorder ranges in predefined order.

    Hi Pete,
    I am sorry for the very belated reply. As you pointed out, the error in the first method was caused by my not specifying named ranges on the source sheet. After I did so, it worked wonders!!!! I also tested the second method, but that gave me errors. I presume that's because I haven't laid out my sheets properly, but I will figure it out. Thank you so much, you saved my life!!!!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.