Storing output in temporary sheet for sorting
Following on from this question, http://stackoverflow.com/questions/10322726/defining-a-range-from-values-in-another-range, (thanks Siddharth!) I want to edit the code to list the the tasks in order by most amount of days to shortest. Had a brief comment chat with Siddharth where he suggested the best way would be to create a temp sheet containing the data, sort that by arrived data and create the message box,  before deleting the temp sheet. Any ideas where to start? Can I export the msg string to a new sheet or does it need to be a variable other t to be stored in a sheet

    Option Explicit

    Sub Notify()
        Dim WS1 As Worksheet
        Dim Chk As Range, FltrdRange As Range, aCell As Range
        Dim ChkLRow As Long
        Dim msg As String
    On Error GoTo WhatWentWrong

    Application.ScreenUpdating = False

    Set WS1 = Sheets("Ongoing")

    With WS1
        ChkLRow = .Range("C" & Rows.Count).End(xlUp).Row

        '~~> Set your relevant range here
        Set Chk = .Range("A1:K" & ChkLRow)

        '~~> Remove any filters
        ActiveSheet.AutoFilterMode = False

        With Chk
            '~~> Filter,
            .AutoFilter Field:=3, Criteria1:="NO"
            '~~> Offset(to exclude headers)
            Set FltrdRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
            '~~> Remove any filters
            ActiveSheet.AutoFilterMode = False

            For Each aCell In FltrdRange
                If aCell.Column = 8 And _
                Len(Trim(.Range("B" & aCell.Row).Value)) <> 0 And _
                Len(Trim(aCell.Value)) <> 0 Then
                    msg = msg & vbNewLine & _
                          "Request for contractor code " & .Range("B" & aCell.Row).Value & _
                          " dispensing month " & .Range("A" & aCell.Row).Value & _
                          " has been in the cupboard for " & _
                          DateDiff("d", aCell.Value, Date) & " days."
                End If
            Next
        End With
    End With

    '~~> Show message
    MsgBox msg
    Reenter:
    Application.ScreenUpdating = True
    Exit Sub
    WhatWentWrong:
    MsgBox Err.Description
    Resume Reenter
    End Sub
Storing output in temporary sheet for sorting
===

Following on from this question, http://stackoverflow.com/questions/10322726/defining-a-range-from-values-in-another-range, (thanks Siddharth!) I want to edit the code to list the the tasks in order by most amount of days to shortest. Had a brief comment chat with Siddharth where he suggested the best way would be to create a temp sheet containing the data, sort that by arrived data and create the message box,  before deleting the temp sheet. Any ideas where to start? Can I export the msg string to a new sheet or does it need to be a variable other t to be stored in a sheet

    Option Explicit

    Sub Notify()
        Dim WS1 As Worksheet
        Dim Chk As Range, FltrdRange As Range, aCell As Range
        Dim ChkLRow As Long
        Dim msg As String
    On Error GoTo WhatWentWrong

    Application.ScreenUpdating = False

    Set WS1 = Sheets("Ongoing")

    With WS1
        ChkLRow = .Range("C" & Rows.Count).End(xlUp).Row

        '~~> Set your relevant range here
        Set Chk = .Range("A1:K" & ChkLRow)

        '~~> Remove any filters
        ActiveSheet.AutoFilterMode = False

        With Chk
            '~~> Filter,
            .AutoFilter Field:=3, Criteria1:="NO"
            '~~> Offset(to exclude headers)
            Set FltrdRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
            '~~> Remove any filters
            ActiveSheet.AutoFilterMode = False

            For Each aCell In FltrdRange
                If aCell.Column = 8 And _
                Len(Trim(.Range("B" & aCell.Row).Value)) <> 0 And _
                Len(Trim(aCell.Value)) <> 0 Then
                    msg = msg & vbNewLine & _
                          "Request for contractor code " & .Range("B" & aCell.Row).Value & _
                          " dispensing month " & .Range("A" & aCell.Row).Value & _
                          " has been in the cupboard for " & _
                          DateDiff("d", aCell.Value, Date) & " days."
                End If
            Next
        End With
    End With

    '~~> Show message
    MsgBox msg
    Reenter:
    Application.ScreenUpdating = True
    Exit Sub
    WhatWentWrong:
    MsgBox Err.Description
    Resume Reenter
    End Sub