title: Sorting worksheets in Excel with VBA using names as dates
body: <p>I am writing a custom sorting procedure for my Excel spreadsheet that has at least 3 worksheets. On first position I put the worksheet called "Summary", on second goes "Data" and the rest are worksheets whose names are dates ex "17.03.2011", "20.03.2011" etc. Those need to be sorted chronologically.</p>
  
  <p><strike>Here is what I have so far, the script stops with an "Object Required" error on line with the DateDiff() and I have no idea why:
  After correcting the code below I am still having trouble in making the thing sort in the right order. Can anyone suggest a way to compare and move around the sheets?</strike></p>
  
  <pre><code>Public Sub ssort()
  sSummary.Move before:=Worksheets.Item(1)
  sData.Move after:=sSummary
  Dim i, n As Integer
  Dim diff As Long
  Dim current, other As Worksheet
  
  For i = 1 To Worksheets.Count
      Set current = Worksheets.Item(i)
      If current.Name &lt;&gt; sData.Name And current.Name &lt;&gt; sSummary.Name Then
  
          For n = i + 1 To Worksheets.Count
              Set other = Worksheets.Item(n)
              diff = DateDiff(DateInterval.day, Format(current.Name, "dd.mm.yyyy"), Format(other.Name, "dd.mm.yyyy"))
              If diff &gt; 0 Then
                  current.Move before:=other
                  Debug.Print "Moving " &amp; current.Name &amp; " before " &amp; other.Name
              ElseIf diff &lt; 0 Then
                  current.Move after:=other
                  Debug.Print "Moving " &amp; current.Name &amp; " after " &amp; other.Name
              End If
          Next n
  
      End If
  Next i
  End Sub
  </code></pre>
  
  <p><strike>I think I either don't understand DateDiff() or Format(), could anyone please shed some light on this?</strike></p>
  
  <p>After modifying code from an online example here <a href="http://www.vbaexpress.com/kb/getarticle.php?kb_id=72" rel="nofollow">http://www.vbaexpress.com/kb/getarticle.php?kb_id=72</a> to use the datediff for comparison, I came up with this solution which works as intended:</p>
  
  <pre><code>Sub sort2()
  sSummary.Move before:=Worksheets.Item(1)
  sData.Move after:=sSummary
  Dim n As Integer
  Dim M As Integer
  Dim dsEnd, lowest As Integer
  Dim dCurrent() As String
  Dim dOther() As String
  Dim diff As Long
  dsStart = 3
  dsEnd = Worksheets.Count
  
  For M = dsStart To dsEnd
      For n = M To dsEnd
          If Worksheets(n).Name &lt;&gt; "Summary" And Worksheets(n).Name &lt;&gt; "Data" And Worksheets(M).Name &lt;&gt; "Summary" And Worksheets(M).Name &lt;&gt; "Data" Then
              dCurrent = Split(CStr(Worksheets(n).Name), ".")
              dOther = Split(CStr(Worksheets(M).Name), ".")
              diff = DateDiff("d", DateSerial(dCurrent(2), dCurrent(1), dCurrent(0)), DateSerial(dOther(2), dOther(1), dOther(0)))
              If diff &gt; 0 Then
                  Worksheets(n).Move before:=Worksheets(M)
              End If
          End If
      Next n
  Next M
  
  End Sub
  </code></pre>