[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.
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:
j = 1 ' start row for destination
for i = 1 to sheets("porder").usedrange.rows.count
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
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!
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
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?
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
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
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!
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
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:
Set r = Range("a:a").Find(Sheets("porder").Cells(i, 1)) ' assumes the name to match is in column A
If r Is Nothing Then
MsgBox Sheets("porder").Cells(i, 1) & " not found"
Else: Set r = Range(r, r.Offset(5, 4)) '6 rows 5 cols change to suit
r.Copy Sheets("sheet2").Cells(j, 1)
j = j + 8 ' leaves 2 blank rows between each imported range, change to suit
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
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!!!!