Sort with Macro expanded

[MsValCalla]

So I am trying to create a button where you can sort by name, except when I do it the way I know how to do it, it only does that column alone. I want all the data from the surrounding columns to stay with the name it was originally with. So my columns from left to right are B5-J5 labeled as Date, Name. Type of Event, Event Details, Duration, Exception, Infraction, Occurrences, and Level of Plan. So if person A is on line C6, with a planned absence, for a total of 6 days, I want that data to stay with them when I click the button to sort it ascending. If clicking it takes Person A's name to C15 now, then that data it at in the other columns before needs to go with it. Does that make sense? I am planning on making 2 buttons. One for the name, one for the date and same task. Any Advice?

 

[Z51]

In your Sort code, make sure the entire range of data is included, which should keep the rest of the columns in sync with the sorting column, e.g.

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ' Column you want sorted below, e.g. C2:C20
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        ' Full range of table, including headers, below
        .SetRange Range("B1:J20")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Adjust the Key:=Range value to B2:B20 or D2:D20 (or other range) for sorting on other columns.

 

[MsValCalla]

So I used that formula and changed Sheet1 to be the name of the sheet that I currently have it named as, and it says "Compile error: Invalid outside procedure". Any idea?

 

[MsValCalla]

Nevermind I realized my dumb on that part. But now it is saying "Run-time error "438': Object doesn't support this property or method" and it highlights everything from ActiveWorkbook to DataOption in yellow.

 

[MsValCalla]

In your Sort code, make sure the entire range of data is included, which should keep the rest of the columns in sync with the sorting column, e.g.

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ' Column you want sorted below, e.g. C2:C20
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        ' Full range of table, including headers, below
        .SetRange Range("B1:J20")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Adjust the Key:=Range value to B2:B20 or D2:D20 (or other range) for sorting on other columns.

So I used that formula and changed Sheet1 to be the name of the sheet that I currently have it named as, and it says "Compile error: Invalid outside procedure". Any idea?

 

[Z51]

Did you put my code into a valid Sub procedure, e.g.

Sub myCodeHere()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ' Column you want sorted below, e.g. C2:C20
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        ' Full range of table, including headers, below
        .SetRange Range("B1:J20")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Invalid outside procedure

Office VBA reference topic

learn.microsoft.com

 

[MsValCalla]

Did you put my code into a valid Sub procedure, e.g.

Sub myCodeHere()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ' Column you want sorted below, e.g. C2:C20
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        ' Full range of table, including headers, below
        .SetRange Range("B1:J20")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Invalid outside procedure

Office VBA reference topic

learn.microsoft.com

This is exactly as I have it:

Sub SortByName()
ActiveWorkbook.Worksheets("MasterAttendanceList").Sort.SortFields.Clear
' Column you want sorted below, e.g. C2:C20
ActiveWorkbook.Worksheets("MasterAttendanceList").Sort.SortFields.Add2 Key:=Range("C6:C1400") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MasterAttendanceList").Sort
' Full range of table, including headers, below
.SetRange Range("B6:J1400")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Z51]

If your headers are in row 6, then change Key:=Range("C6:C1400") to Key:=Range("C7:C1400") and leave the reference to .SetRange Range("B6:J1400") alone.

If your headers are in row 5, then leave Key:=Range("C6:C1400") alone, and change .SetRange Range("B6:J1400") to .SetRange Range("B5:J1400").

If that still doesn't fix it, try recording a macro of you selecting the whole range, then sorting it as you want. Stop recording the macro then review the code it generated in the VB Editor (under Modules > Module1 for the workbook).