Adding a New Worksheet to the End of the Workbook

I can think of five ways to add a new worksheet to a workbook. There may be more, but I can only think of five.

Alt + i + w – this is the way I do it now. I’m trying to get away from the 2003 keyboard shortcuts, but this one remains.
Alt + h + i + s – this is what I should be using because it’s on the Ribbon, but it’s also one extra key.
Click the Insert Worksheet “tab” to the right of all the real sheets.
Use the Shift + F11 keyboard shortcut for the Insert Worksheet “tab” that inexplicably behaves differently than clicking the tab.
Right clicking on a sheet tab and choosing Insert… and going through the dialog box.

Only one of these five methods inserts the worksheet to the right of the active sheet, kind of. #3, the mouse only one, inserts a worksheet at the end of all sheets. All the other methods, including Shift + F11, insert a worksheet to the left of the active worksheet. I’m not much of a clicky guy as you know, preferring the keyboard. But sometimes I want the new worksheet to be at the end. So what’s a guy to do? Acquiesce and reach for the mouse? I don’t think so.

I have an add-in called UIHelpers.xlam. In that add-in is a CAppEvents class for controlling application level events. One event that I’m now using is the Application_WorkbookNewSheet event. It listens for when a new sheet is added to any workbook.

Private Sub mxlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

    If Sh.Index = Wb.Sheets.Count - 1 Then
        Sh.Move , Wb.Sheets(Wb.Sheets.Count)
    End If

End Sub

Private Sub mxlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

If Sh.Index = Wb.Sheets.Count - 1 Then

Sh.Move , Wb.Sheets(Wb.Sheets.Count)

End If

End Sub

If the new sheet is the penultimate sheet, move it to the end. When I’m on the last sheet and insert a new sheet, more often than not I want the new sheet to be to the right. There are a few times when that’s not true and I’ll have to move them. But this will cut down on manually moving worksheets significantly.

6 thoughts on “Adding a New Worksheet to the End of the Workbook”

In Excel 13 method#3 adds a new sheet to the right of the active worksheet

In Excel13 you can just click on the plus icon to add a sheet to the very end, but I always use shift-f11 and then manually move it I want it at the end.

Actually, I’m a complete liar, it just adds it after. But Dick, I’m intrigued by this add-in. For those are that way inclined you could throw them an inputbox to name the sheet as well? How would I go about finding this add-in and using it for myself?

Rich: It’s my Personal Macro Workbook, I just made it an add-in. It has all manner of stuff in there, some tested, some not. But if you want to poke around in it, you’re welcome to it.

http://dailydoseofexcel.com/excel/UIHelpers.zip