title: Storing output in temporary sheet for sorting
body: <p>Following on from this question, <a href="https://stackoverflow.com/questions/10322726/defining-a-range-from-values-in-another-range">Defining a range from values in another range</a>, (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</p>
  
  <pre><code>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" &amp; Rows.Count).End(xlUp).Row
  
      '~~&gt; Set your relevant range here
      Set Chk = .Range("A1:K" &amp; ChkLRow)
  
      '~~&gt; Remove any filters
      ActiveSheet.AutoFilterMode = False
  
      With Chk
          '~~&gt; Filter,
          .AutoFilter Field:=3, Criteria1:="NO"
          '~~&gt; Offset(to exclude headers)
          Set FltrdRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
          '~~&gt; Remove any filters
          ActiveSheet.AutoFilterMode = False
  
          For Each aCell In FltrdRange
              If aCell.Column = 8 And _
              Len(Trim(.Range("B" &amp; aCell.Row).Value)) &lt;&gt; 0 And _
              Len(Trim(aCell.Value)) &lt;&gt; 0 Then
                  msg = msg &amp; vbNewLine &amp; _
                        "Request for contractor code " &amp; .Range("B" &amp; aCell.Row).Value &amp; _
                        " dispensing month " &amp; .Range("A" &amp; aCell.Row).Value &amp; _
                        " has been in the cupboard for " &amp; _
                        DateDiff("d", aCell.Value, Date) &amp; " days."
              End If
          Next
      End With
  End With
  
  '~~&gt; Show message
  MsgBox msg
  Reenter:
  Application.ScreenUpdating = True
  Exit Sub
  WhatWentWrong:
  MsgBox Err.Description
  Resume Reenter
  End Sub
  </code></pre>