VBA: Sort

[srosk]

Probably one of the easier questions. I tried to record a macro and copy the code.. didn't work so well. Sooo... here we go! There are 4 sheets, each has a header. Do I need to specify all columns that exist in the sheet? There is a filter present (no criteria specified) - if that makes it easier

Tab name: criteria
VDR: sort by data in column B (a to z)
DDR: sort by data in column B (a to z)
VOR: sort by data in column B and then colum D cell highlight (standard yellow)
TDR: sort by data in column h (high to small), then column I (z to a)

Thank you in advance for whatever assistance you can provide!

 

[JLGWhiz]

I don't know if it is my system or if MS has done something to the sort routine, but I had some alert messages that should not have appeared to pop up when running this. You can geve it a try and see if it will run without the messages popping up. The code is correct, but it acts like it did not care for the way the sheet was set up. Maybe your actual data will be more to its liking.

Sub t()
Sheets("VDR").UsedRange.Sort Range("B1"), xlAscending, Header:=xlYes
Sheets("DDR").UsedRange.Sort Range("B1"), xlAscending, Header:=xlYes
Sheets("VOR").UsedRange.Sort Range("B1"), xlAscending
With Sheets("VOR")
    .Range("D1", .Cells(Rows.Count, 4).End(xlUp)).Interior.Color = vbYellow
End With
Sheets("TDR").UsedRange.Sort Range("H1"), xlDescending, , Range("I1"), xlDescending, Header:=xlYes
End Sub

 

[srosk]

Thank you!!

For the VOR, it looks to be highlighting column 4 and not sorting for some reason. I checked the tips on Mr excel and found this code:

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _
        xlSortOnCellColor, xlAscending, , _
        xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)

I modified it to:

ActiveSheet.UsedRange.Sort.SortFields.Add(Range("D2"), _
        xlSortOnCellColor, xlAscending, , _
        xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)

It keeps erroring. Now it says: object doesn't support this property or method


What am I doing wrong?

 

[JLGWhiz]

Your original post indicates only column B for sort and column D for highlight only on sheet VOR. Did you want bolth columns B and D sorted? If so then

Sheets("VOR").UsedRange.Sort Range("B1"), xlAscending, , Range("D1"), xlAscending, Header:=xlYes

 

[srosk]

Your original post indicates only column B for sort and column D for highlight only on sheet VOR. Did you want bolth columns B and D sorted? If so then

Sheets("VOR").UsedRange.Sort Range("B1"), xlAscending, , Range("D1"), xlAscending, Header:=xlYes

First sort by B, and then put cells with yellow highlight up top on D.. You gave the following code:

With Sheets("VOR")
    .Range("D1", .Cells(Rows.Count, 4).End(xlUp)).Interior.Color = vbYellow
End With

The issue is, it highlights all of D. I just want to sort by D. I also need to use 'ActiveSheet.' instead of calling the sheet, if possible. That is causing issues when naming the specific sheet.

 

[srosk]

I think I got it...

I appreciate you pointing me in the right direction!

ActiveSheet.UsedRange.Sort Range("B1"), xlAscending, Header:=xlYes

    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add(Range( _
        "D:D"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
        = RGB(255, 255, 0)
    With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[JLGWhiz]

Thanks for the feedback,
Regards, JLG