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.

  1. 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.
  2. Alt + h + i + s – this is what I should be using because it’s on the Ribbon, but it’s also one extra key.
  3. Click the Insert Worksheet “tab” to the right of all the real sheets.
  4. Use the Shift + F11 keyboard shortcut for the Insert Worksheet “tab” that inexplicably behaves differently than clicking the tab.
  5. 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

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.

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

  1. 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.

  2. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *