How to Always Add a New Excel Worksheet at the End of the Workbook
Question details
The user wants to force Excel to always insert new worksheets at the very end of the workbook tabs, rather than next to the currently active sheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Adding a new worksheet to a workbook that contains multiple tabs, where the user wants the new sheet to strictly appear as the last tab on the right.
- Observed behavior
- By default, Excel inserts a new worksheet immediately to the right of the currently active sheet, and there is no built-in setting to change this default placement.
Before adding macros, ensure the Developer tab is enabled in your Excel ribbon and that you are prepared to save your file as a Macro-Enabled Workbook (.xlsm).
Use a VBA Macro to Insert Sheets at the End
Create a custom macro that explicitly overrides the default behavior by adding a new worksheet after the final tab in your workbook.
Since Excel does not have a native setting to change where new sheets are added, a simple VBA script can be used to count the total number of sheets and append the new one at the very end. By storing this macro in your Personal Macro Workbook (PERSONAL.XLSB), you can use it across all your Excel files.
Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
In the Project Explorer pane on the left, look for 'VBAProject (PERSONAL.XLSB)'. Right-click it, select 'Insert', and click 'Module'.
Copy and paste the following code into the module window: Sub NewSheet() On Error GoTo ErrHandler Worksheets.Add After:=Sheets(Sheets.Count) Exit Sub ErrHandler: Beep End Sub
Click the Save icon to save your Personal Macro Workbook, then close the VBA editor to return to Excel.
Go to File > Options > Quick Access Toolbar. Under 'Choose commands from', select 'Macros'. Find 'PERSONAL.XLSB!NewSheet', click 'Add', and then click 'OK'. You can now click this button anytime to add a sheet at the end.
Experience seamless spreadsheet management with WPS Office
If you find Microsoft Excel's rigid default settings frustrating, consider trying WPS Office. It provides an intuitive, user-friendly interface for managing workbooks and is fully compatible with Microsoft Office files.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install the Software: Run the downloaded installer and follow the simple on-screen instructions to complete the setup.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files to enjoy seamless compatibility and fast performance.
Frequently Asked Questions
Why doesn't Excel add new sheets at the end by default?
Excel's default design inserts new worksheets next to the currently active sheet to maintain context, assuming users generally want to keep new data adjacent to their current work. Microsoft has not provided a built-in toggle to change this behavior.
What is PERSONAL.XLSB and why do I need it?
PERSONAL.XLSB is a hidden Personal Macro Workbook that opens automatically in the background every time you start Excel. Storing your 'add new sheet' macro here ensures the shortcut works in any workbook you open, rather than just the specific file where you wrote the code.
Will this macro work if I share my workbook with others?
If you store the macro in your PERSONAL.XLSB, it will only work on your computer. If you want the functionality to be available to anyone who opens a specific workbook, you must save the code in a standard Module within that specific workbook and save the file as a Macro-Enabled Workbook (.xlsm).
