McGimpsey & Associates : Excel : Insert Worksheets to the Right

This site will look better if you upgrade to a browser that supports web standards.

Inserting worksheets to the right

XL's Insert/Worksheet menu 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.

Insert to the right of the active sheet

This macro can be attached to a keyboard shortcut, or a toolbar button. I modify the worksheet tab menu's Insert... command to call this (see below). There are two flavors of adding worksheets. The first adds the factory default worksheet:

    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

Insert to the right of all other sheets

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.

Modifying XL's menu bars to insert sheets to the right

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 Insert/Worksheet calls the InsertWorksheetAfterAll macro, to insert sheets to the right of the right-most sheet. Right-clicking the worksheet tab and choosing Insert... calls the InsertWorksheetAfter macro to insert a single sheet to the right of the active sheet.

This page last updated Thursday, 25 March 2004

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

Inserting worksheets to the right

Insert to the right of the active sheet

Insert to the right of all other sheets

Modifying XL's menu bars to insert sheets to the right

Quick Links