Sorting Macro

[Jamie]

Hi,

I have a worksheet that has multiple tabs in it. Each tabe is formated the
same way and pulls all of its data via the vlookup formula. Within a tab
there are headings such as

A B C D
Provider # Client Name Beds Occupancy

The tab uses the "provider number" to pull in the appropriate data for that
particular client. Within that tab the information is also sorted by county
and then subtotaled. For example: The above layout may have Cuyahoga county
with 5 clients listed. Below the list of clients there will be a row totaling
the clients within cuyahoga only. then there will be a space and the next
county will start, such as Summit county and so on.

What I need to do is sort each county by its occupancy from highest to
lowest. Since the worksheet is so large it will take much to long to manually
sort therefore I was trying to make a macro. The issue I keep running into is
the macro does not remember that I want to sort "column D" by "highest to
lowest." The macro runs once and then the sort function defaults back to
sorting column A by A to Z.

Let me know if you think this is something that can be fixed or if it is
just an Excel glitch that I can't get around.

Thanks,

Jamie

 

[Paul C]

A simple sort code would look like this

ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add
Key:=Range("C2:C5"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add
Key:=Range("D25"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet4").Sort
.SetRange Range("A15")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

Could you post your section of code?

 

[Jamie]

I created my macro by simply hitting the record button and then performed the
specific key strokes in order to perform my task. The problem arose when I
went to run it the next time the key strok I used to "custom sort" returned
to the default setting and sorted my data that way.

If it helps here it what the macro looks like:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Columbiana").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Columbiana").Sort.SortFields.Add
Key:=ActiveCell. _
Offset(0, 9).Range("A1:A3"), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Columbiana").Sort
.SetRange ActiveCell.Range("A1:W3")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub

 

[Paul C]

I think the problem is with this section.
AddKey:=ActiveCell.Offset(0, 9).Range("A1:A3")

And it looks like you are looping through and manually selection each section.

You could to the looping automatically
Something like this (I assumed A2 is the first data cell and A1 is the header)

Sub Macro1()

Range("A2").Select
Do While ActiveCell <> Empty
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Columbiana").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Columbiana").Sort.SortFields.Add
Key:=Range("D13"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Columbiana").Sort
.SetRange ActiveCell.Range("A1:W3")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Selection.End(xlDown).Select
Loop
Cells(lastrow, 1).Select
End Sub

If you paste everything except the Sub and End Sub lines between your
current Macro you can keep the C

 

[Paul C]

the last part should read you can keep your current Ctrl + l shortcut intact