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