How to copy and sort a range of names from a list

[Jeddo]

I have a data base with names and I am needing to find all the names within a range (H - L for example), copy them to another range and sort them in alphabetical order. Novice with Excel. Tried doing a search with the greater than and lesser than signs, but cna't get the syntax right or even the formula right. I have mastered copying everything and sorting it alphabetically in a new range, just can't figure out how to pull out a range of names

Alpha		Hotel
Tango		India
Hotel		Juliet
Oscar		Kilo
Whiskey		Lima
Bravo
Juliet
Quebec
X-Ray
Mike
Foxtrot
Lima
Zulu
Charlie
Kilo
Romeo
Victor
Delta
India
Papa
Uniform
Yankee
Echo
Golf
November
Sierra

[/RANGE

 

[Jeddo]

Does Fluff's formula-based reply work for you, or would you still like to pursue a VBA option?

If VBA, please answer this earlier question I posted:

It will become much easier to do if we are able to re-sort the original list.

My apologies, I already have a range I've copied the list to and have it sorted alphabetically. I made a mistake thinking it would give a clearer idea of what I was trying to do if I showed the original list random. I'm not at my computer, so I can't redo the example. The original list should have been shown already sorted alphabetically to give a truer picture. Thank you for your patience.

 

[Joe4]

OK, in looking at your code, you seem to have a lot of other things going on there, like combo boxes and the like. I also get the impression that you may have "simplified" the data a bit for this example. So it is very difficult to follow the flow of your code without seeing your data, where exactly it resides on your sheets, and what else is on the sheet. I am a little concerned that you may have a hard time implementing any solution I provide to you within your structure.

Without having access to your worksheet, I think we are going to need to know a bit more to give you a solution that you can "plug-and-play" with minimal adapting.
Can you provide the following information:
1. What sheet is your original data on?
2. What is the exact range (addresses) where this data resides?
3. Are the four groupings you want the results to appear on on the exact same sheet as your original data, or different sheets (if different sheets, please provide sheet names)?
4. Exactly what range (addresses) should these results go in?
5. Will it ALWAYS be broken up into these EXACT 4 groups (A-G, H-L, M-R, S-Z), or is that variable?

 

[Jeddo]

OK, in looking at your code, you seem to have a lot of other things going on there, like combo boxes and the like. I also get the impression that you may have "simplified" the data a bit for this example. So it is very difficult to follow the flow of your code without seeing your data, where exactly it resides on your sheets, and what else is on the sheet. I am a little concerned that you may have a hard time implementing any solution I provide to you within your structure.

Without having access to your worksheet, I think we are going to need to know a bit more to give you a solution that you can "plug-and-play" with minimal adapting.
Can you provide the following information:
1. What sheet is your original data on?
2. What is the exact range (addresses) where this data resides?
3. Are the four groupings you want the results to appear on on the exact same sheet as your original data, or different sheets (if different sheets, please provide sheet names)?
4. Exactly what range (addresses) should these results go in?
5. Will it ALWAYS be broken up into these EXACT 4 groups (A-G, H-L, M-R, S-Z), or is that variable?

1. Original data is on sheet phonelist
2. Data range is CA9:CG233.
3. All data will appear on the same sheet
4. A-G range is CT9:CZ70
H-L range is DB9:DH70
M-R range is DJ9:DP70
S-Z range is DR9:DX70
Note: None of these ranges including the original data includes the header, which is on row 8
5. I don't anticipate having to change the groupings.

 

[Joe4]

OK, give me a little time, and I will see what I can come up with.

 

[Joe4]

Try this:

Sub MyCopyMacro()

    Dim lr As Long
    Dim sg As Long
    Dim lg As Long
    Dim i As Long
    Dim lastLet
    Dim lt As String
    Dim fc As Long
    Dim r As Long
        
    Application.ScreenUpdating = False
    
'   Set array of letters, specifically the last letter in each grouping
    lastLet = Array("G", "L", "R", "Z")
    
'   Find last row in column CA with data
    lr = Cells(Rows.Count, "CA").End(xlUp).Row
    
'   Sort original data range
    Range("CA8:CG" & lr).Sort Key1:=Range("CA8"), Order1:=xlAscending, Key2:=Range("CB8") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
        
'   Initialize variables
    i = 0   'initial group
    sg = 9  'starting row of data grouping
    fc = 98 'first column to paste to
    
'   Loop through data
    lt = lastLet(i)
    For r = 9 To lr
'       See if names falls outside the range
        If Left(Cells(r, "CA"), 1) > lt Then
'           Set last row for group
            lg = r - 1
'           Copy data to new range
            Range(Cells(sg, "CA"), Cells(lg, "CG")).Copy Cells(9, fc + (i * 8))
'           Increment group
            i = i + 1
'           Get new group ending letter
            lt = lastLet(i)
'           Set new starting number
            sg = r
        End If
    Next r
    
'   Copy last group
    Range(Cells(sg, "CA"), Cells(lr, "CG")).Copy Cells(9, fc + (i * 8))
    
    Application.ScreenUpdating = True
    
    MsgBox "Complete!"
    
End Sub

I added lots of documentation to the code to explain what it is doing.

 

[Jeddo]

Try this:

Sub MyCopyMacro()

    Dim lr As Long
    Dim sg As Long
    Dim lg As Long
    Dim i As Long
    Dim lastLet
    Dim lt As String
    Dim fc As Long
    Dim r As Long
       
    Application.ScreenUpdating = False
   
'   Set array of letters, specifically the last letter in each grouping
    lastLet = Array("G", "L", "R", "Z")
   
'   Find last row in column CA with data
    lr = Cells(Rows.Count, "CA").End(xlUp).Row
   
'   Sort original data range
    Range("CA8:CG" & lr).Sort Key1:=Range("CA8"), Order1:=xlAscending, Key2:=Range("CB8") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
       
'   Initialize variables
    i = 0   'initial group
    sg = 9  'starting row of data grouping
    fc = 98 'first column to paste to
   
'   Loop through data
    lt = lastLet(i)
    For r = 9 To lr
'       See if names falls outside the range
        If Left(Cells(r, "CA"), 1) > lt Then
'           Set last row for group
            lg = r - 1
'           Copy data to new range
            Range(Cells(sg, "CA"), Cells(lg, "CG")).Copy Cells(9, fc + (i * 8))
'           Increment group
            i = i + 1
'           Get new group ending letter
            lt = lastLet(i)
'           Set new starting number
            sg = r
        End If
    Next r
   
'   Copy last group
    Range(Cells(sg, "CA"), Cells(lr, "CG")).Copy Cells(9, fc + (i * 8))
   
    Application.ScreenUpdating = True
   
    MsgBox "Complete!"
   
End Sub

I added lots of documentation to the code to explain what it is doing.

Thank you so much. I'll try it asap. Probably won't be till tomorrow evening before I can give it a go. I'll let you know how it goes. Appreciate the documentation. The tutorial video I learned from didn't show it or stress the importance enough. Only learned later the hard way how helpful it can be.

 

[Jeddo]

Thank you so much. I'll try it asap. Probably won't be till tomorrow evening before I can give it a go. I'll let you know how it goes. Appreciate the documentation. The tutorial video I learned from didn't show it or stress the importance enough. Only learned later the hard way how helpful it can

 

[Jeddo]

Success! Couldn't wait till tonight, so I stayed up late to play with it. Didn't work at first, but then I realized that dumb me didn't inform you that the column needing sorted in the range was the second column, not the first. Changed CA to CB in three spots and it worked as advertised. But ran into another problem, again from not providng enough information. This program is for a charity fund raising event and I am trying to get it to the point where it is user friendly so to say, in case I am not able to attend for some reason. For that reason, I was wanting to implant it into the code I have to print out the four ranges. Thought it would be a simple thing as dropping the macro in the line of code somewhere, but no matter where I place it, the macro stops at the:

Range("CA8:CG" & lr).Sort Key1:=Range("CA8"), Order1:=xlAscending, Key2:=Range("CB8") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

This is the code I am wanting to insert it into. Don't laugh to hard at it.

Private Sub cmdPrintBreakout_Click()

If MsgBox("Do you want to print out Breakdown Lists?", vbQuestion + vbYesNo) <> vbYes Then
Exit Sub

End If
'Print out by Buyer's Last Name, broken down by A-G, H-L, M-R, S-Z.
'Data has to be manually entered at this time before printing

With Sheet1
Application.PrintCommunication = False
With .PageSetup

.BottomMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0#)
.TopMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0#)
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)

.PrintArea = Sheet1.Range("CT5:CZ70").Address
.PrintArea = Sheet1.Range("DB5:DH70").Address
.PrintArea = Sheet1.Range("DJ5:DP70").Address
.PrintArea = Sheet1.Range("DR5:DX70").Address
End With
Application.PrintCommunication = True

Sheet1.Range("CT5:CZ70").PrintOut
Sheet1.Range("DB5:DH70").PrintOut
Sheet1.Range("DJ5:DP70").PrintOut
Sheet1.Range("DR5:DX70").PrintOut

End With
End Sub

Is what I am wanting to do possible? I get the feeling I need a buffer or something to give the macro time to run before the print starts.

Thank you so much for your help, it is very much appreciated. I can see I've got some study time ahead of me in dissecting and understanding the code you sent. And I also now understand how important it is to give a complete picture of what you are wanting.

A Merry Christmas and Happy New Year to you and those around you.

 

[Joe4]

It seems that the original issue was resolved and this printing thing seems to be a whole new/different question, in which case it is advised that you post a new question for that.

That way others can see it as a new unanswered question, and can reply too (which will be good, especially since I am going to be in-and-out a lot over the next week with the holidays).

 

[Jeddo]

It seems that the original issue was resolved and this printing thing seems to be a whole new/different question, in which case it is advised that you post a new question for that.

That way others can see it as a new unanswered question, and can reply too (which will be good, especially since I am going to be in-and-out a lot over the next week with the holidays).

Understand. Thank you again.