[Excel VBA] How to Always Add a New Worksheet to the End (Far Right)

Introduction

When you add a new sheet in Excel, it is usually inserted to the left of the currently active sheet. However, there are many cases—such as creating monthly summary sheets or work logs—where you want to “always add the sheet to the far right (the end).”

Manually dragging the sheet to the right edge every time is a tedious task.

By creating a macro with VBA, you can complete this task with a single button press. In this article, I will introduce simple and reliable code to achieve this.

VBA Sample Code to Add a Sheet to the End

You can add a new sheet using the Worksheets.Add method. By specifying the After argument, you can control exactly where the sheet is added.

To add a sheet to the end, you simply command it to be added “After the last sheet.”

' Add a new worksheet to the end of the workbook
Sub AddNewSheetAtEnd()

    ' Define variable
    Dim sheetCount As Long
    
    ' Get the current number of sheets
    sheetCount = ThisWorkbook.Worksheets.Count
    
    ' Add a new sheet after the last sheet
    Worksheets.Add After:=Worksheets(sheetCount)
    
    MsgBox "Added a new sheet to the end.", vbInformation

End Sub

How to Use

1. Open the VBE (Alt + F11) and paste the code above into a Standard Module.
2. Run the AddNewSheetAtEnd macro.
3. A new sheet will be added to the far right of the workbook.

Explanation of the Code

Dim sheetCount As Long

Declares a variable sheetCount to store the number of sheets.

sheetCount = ThisWorkbook.Worksheets.Count

Uses the Worksheets.Count property to get the total number of sheets in the workbook and assigns it to the variable sheetCount. For example, if there are 5 sheets, 5 is stored.

Worksheets.Add After:=Worksheets(sheetCount)

This is the core of the process.

• Worksheets.Add: The command to add a new sheet.
• After:=: The argument used to add a sheet “after” a specific sheet.
• Worksheets(sheetCount): Specifies the “last sheet” (e.g., the 5th sheet if there are 5 total).

Overall, this tells Excel to “Add a new sheet after the last sheet,” ensuring it always appears at the far right.

Summary

In this article, I introduced a simple and practical macro to always add a new sheet to the end of a workbook.

• The After argument of the Worksheets.Add method is the key.
• Use Worksheets.Count to identify the last sheet.

Just remembering this logic makes daily sheet addition tasks much easier. I recommend registering this code in your Personal Macro Workbook so you can use it with any file.