This site will look better if you upgrade to a browser that supports web standards.
XL'smenu command inserts sheets to the left of the active sheet. This is consistent with the metaphor of the other Insert items - for instance, if you insert a column, the current column and all columns to the right move right to accommodate the new one..
More commonly, however, people like to insert worksheets either to the right of the active sheet or after all other sheets. This page demonstrates both techniques.
This macro can be attached to a keyboard shortcut, or a toolbar button. I modify the worksheet tab menu's see below). There are two flavors of adding worksheets. The first adds the factory default worksheet:command to call this (
Public Sub InsertWorksheetAfter() ActiveWorkbook.Worksheets.Add After:=ActiveSheet End Sub
the second adds a sheet based on a template that you have stored in the startup directory (or the default, if you don't have a template).
Public Sub InsertWorksheetAfter() ActiveWorkbook.Sheets.Add After:=ActiveSheet, Type:="worksheet" End Sub
This macro inserts sheets all the way to the right. As a bonus, if you have more than one sheet selected, that number of sheets will be added. Note that I loop, rather than using the .Count argument of the Add method - using .Count causes the default sheet numbers to display in reverse order.
Public Sub InsertWorksheetAfterAll() Dim nLast As Long Dim i As Long With ActiveWorkbook nLast = .Sheets.Count For i = 1 To ActiveWindow.SelectedSheets.Count .Sheets.Add _ After:=.Sheets(.Sheets.Count), _ Type:="worksheet", _ Count:=1 Next i .Sheets(nLast + 1).Select End With End Sub
Of course, you can use Worksheets.Add instead if you choose.
I use an add-in in the Startup directory to configure my environment (I could have used my Personal Macro Workbook, or Personal.xls for WinXL). As part of that add-in, I put the macros above in a regular code module. I also call this macro from the Workbook_Open() event macro:
Public Sub ReplaceInsertWorksheetActions() CommandBars("Ply").Controls("Insert...").OnAction = _ "InsertWorksheetAfter" CommandBars("Worksheet Menu Bar").Controls( _ "Insert").Controls("Worksheet").OnAction = _ "InsertWorksheetAfterAll" End Sub
After running this code, choosing InsertWorksheetAfterAll macro, to insert sheets to the right of the right-most sheet. Right-clicking the worksheet tab and choosing calls the InsertWorksheetAfter macro to insert a single sheet to the right of the active sheet.calls the
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.