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.
1 2 3 4 5 6 7 |
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.
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
Nice. And you used the word “penultimate,” making it awesome.
Thanks so much for the add-in, it’s a rare piece of golden Excel goodness and it teaches some fundamentals of getting started with add-ins.
I came for a quick-fix to the annoyance new worksheet location… and instead got educated. 😉