Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
2  
Just FYI - all of those .Select are superfluous – Raystafarian Jan 8 at 17:48
up vote 11 down vote accepted

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.

share|improve this answer
    
thanks jonno, that is what i was looking for. appreciate the help. – DanM Jan 8 at 17:18
    
@DanM No problem, glad it worked for you. – Jonno Jan 8 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)  
NavigationTwoSheet.Select

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .