Creating a sorting Macro using Excel VBA

Aim: To create a user-friendly sorting macro using excel VBA to edit the excel worksheet

Manual sorting of excel sheets is an arduous and time-consuming task furthermore some people might not be familiar with the sorting tool in excel. In such a scenario, creating a sorting macro using VBA would save time and effort. Even people who might not be familiar with manual sorting could use the micro with the click of a button.

Procedure: The script uses the feature of the "record macro button" to make use of the excel inbuilt feature to automatically write lines of code as per the user's actions on the worksheet. This feature is used for creating sub-procedures involving the actual sorting part. The sub-procedures are named as per the column they are sorting with.

Next, a manual script is typed in the sub-procedure "sort by()" to get the user's input as to which column or data the user wants to sort by. This input is then used to call the above-mentioned pre-recorded macros. If the input entered by the user is not meaningful, a message box is shown asking the user if they want to continue or not (using the vbyesNo feature)

VBA script:

Option Explicit

Sub DateThenTime()
'
' DateThenTime Macro
' Sorts table by date and then time.
'

'
    Range("A5:G78").Select
    Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End Sub
Sub RepSort()
'
' RepSort Macro
' Sorts by Rep, Date, and then Time
'

'
    
    Range("A5:G78").Select
    Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Key2:=Range("A5") _
        , Order2:=xlAscending, Key3:=Range("B5"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
End Sub
Sub ClientDateTime()
'
' ClientDateTime Macro
' Sorts by Client, Date, Time
'

'
    
    Range("A5:G78").Select
    Selection.Sort Key1:=Range("D5"), Order1:=xlAscending, Key2:=Range("A5") _
        , Order2:=xlAscending, Key3:=Range("B5"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
End Sub
Sub ProductDateTime()
'
' ProductDateTime Macro
' Sorts by product, date, and then time
'

'
    
    Range("A5:G78").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Key2:=Range("A5") _
        , Order2:=xlAscending, Key3:=Range("B5"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
End Sub
Sub ProblemProduct()
'
' ProblemProduct Macro
' Sorts by problem and then product.
'

'
    
    Range("A5:G78").Select
    Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Key2:=Range("E5") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End Sub
Sub SortBy()
'
'
'
Dim Message, TitleBarTxt, DefaultTxt, SortVal As String
Dim YNAnswer As Integer

Message = "Enter a number to sort by the following fields:" & vbCrLf & _
" 1 - By Date and Time" & vbCrLf & _
" 2 - By Customer Service Rep, Date & Time" & vbCrLf & _
" 3 - By Client, Date & Time" & vbCrLf & _
" 4 - By Product, Date & Time"

TitleBarTxt = "Sort Call Center log"
DefaultTxt = "Enter 1, 2, 3 or 4"

SortVal = InputBox(Message, TitleBarTxt, DefaultTxt)

Select Case SortVal
    Case "1"
        Call DateThenTime
    Case "2"
        Call RepSort
    Case "3"
        Call ClientDateTime
    Case "4"
        Call ProductDateTime
    Case Else
        YNAnswer = MsgBox("Youdidnt type 1, 2, 3 or 4. Try Again?", vbYesNo)
        If YNAnswer = vbYes Then
            Call SortBy
        End If
        
End Select


End Sub

Result: A VBA script has been successfully written, compiled and tested for bugs. The macro has been tested for all inputs and the excel worksheets successfully sorted.