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 Comments

  1. sam says:

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

  2. Rich says:

    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.

  3. Rich says:

    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?

  4. Dick Kusleika says:

    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

  5. ChipG says:

    Nice. And you used the word “penultimate,” making it awesome.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: