How do I get the last worksheet in Excel VBA?

Question

I have a sheet called "End" that needs to be moved to be the last of all available sheets. I need to do this using VBA.

I have a sort routine that works, I just need the syntax for the move. Here is what I have:

Private Sub CommandButton263_Click()
Dim i As Integer
Dim j As Integer
For i = 1 To Sheets.Count
  For j = 1 To Sheets.Count - 1
        If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
           Sheets(j).Move After:=Sheets(j + 1)
        End If
  Next j
Next i
Sheets("Navigation").Select
Sheets("Navigation").Move Before:=Sheets(1)
Sheets("Navigation (2)").Select
Sheets("Navigation (2)").Move Before:=Sheets(2)
Sheets("EstimateTemplate").Select
Sheets("EstimateTemplate").Move Before:=Sheets(3)
Sheets("Formulas").Select
Sheets("Formulas").Move Before:=Sheets(4)
Sheets("End").Move After:=Sheets(EOF)  'what can i use instead of EOF?
Sheets("Navigation (2)").Select
End Sub

Specifically looking at line Sheets("End").Move After:=Sheets(EOF) - What can I use to get the last sheet?

Answer 1

Sheets("End").Move after:=Worksheets(Worksheets.Count)

Worksheets.Count will return the total amount of sheets, and Worksheets(index) selects a worksheet at a specific number. Combining both, you should get the last one, whatever it is.

Answer 2

I would first rename your worksheets VBA CodeName property, to be meaningful. Instead of "Sheet1" or whatever they are, make them meaningful modifiers

Right now, if someone changes the sheet name, your VBA breaks.

If your sheet is instead has a CodeName you can do very nice things like:

EndSheet.Move after:=Worksheets(Worksheets.Count)

This means even if someone renames that worksheet, say to "breakin your macro!" it won't... break your macro.

Also, regarding Selects, you can do this to make your code more reliable, faster, and robust:

NavigationSheet.Move Before:=Sheets(1)
NavigationTwoSheet.Move Before:=Sheets(2)
EstimateTemplateSheet.Move Before:=Sheets(3)
FormulasSheet.Move Before:=Sheets(4)
EndSheet.Move After:=Sheets(EOF)  
NavigationTwoSheet.Select

(this assumes you've renamed all your worksheets CodeName in the VBE)