Monday, April 27, 2015

Reverse the Order of Worksheets in an Excel Workbook

Today I found myself wanting to reverse the order of sheet tabs in an Excel file.  The VBA code snippet below does just that.

Sub Worksheet_Reverse_Order()
Dim MySheet As Worksheet
Dim i As Integer

For Each MySheet In Worksheets
Debug.Print MySheet.Index, MySheet.Name
Next

For i = 2 To Worksheets.Count
Set MySheet = Worksheets(i)
MySheet.Move before:=Worksheets(1)
Next i

For Each MySheet In Worksheets
Debug.Print MySheet.Index, MySheet.Name
Next

End Sub


Background...
I had been maintaining weekly status updates as Excel spreadsheets, all grouped into one XLS document.  Each week I'd add a new sheet to the right of the previous week's worksheet tab.

But due to a change in workflow, I now have to copy the new sheet into that workbook rather than create it in the workbook.  In order to keep placing the new sheet after all the others, it's necessary to scroll to the end of the list of sheets and select "(move to end)".

"It would be so much easier if the sheets were in reverse order," I sighed to myself.  The thought of dragging them into reverse order manually was, well, unthinkable.  And thus this VBA macro was born.