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").Move Before:=Sheets(1)
Sheets("Navigation (2)").Select
Sheets("Navigation (2)").Move Before:=Sheets(2)
Sheets("EstimateTemplate").Move Before:=Sheets(3)
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?

  • 2
    Just FYI - all of those .Select are superfluous Jan 8 '16 at 17:48

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.

  • thanks jonno, that is what i was looking for. appreciate the help.
    – DanM
    Jan 8 '16 at 17:18
  • @DanM No problem, glad it worked for you.
    – Jonno
    Jan 8 '16 at 17:19

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)  

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.