5 Easy Steps to Add Sheets at End in Excel VBA

Ever found yourself needing to add multiple sheets to an Excel workbook without manually clicking through the interface? Whether you're a data analyst, an accountant, or just someone who deals with a lot of spreadsheets, knowing how to automate repetitive tasks can save you an immense amount of time. In this blog post, we'll guide you through 5 easy steps to add sheets at the end of an Excel workbook using VBA.

Step 1: Opening the Visual Basic Editor

To get started, you’ll need to access the Visual Basic for Applications (VBA) editor. Here’s how:

• Open your Excel workbook.
• Press Alt + F11 to open the VBA editor. This shortcut works universally across all versions of Excel.

💡 Note: If you’re using a laptop or a keyboard without a function key (F11), you might need to hold the ‘Fn’ key as well.

Step 2: Creating a New Module

Once you’re in the VBA editor, you need a place to write your code:

• In the Project Explorer (usually on the left side), right-click on any of your workbook’s components.
• Select Insert > Module. This will create a new module where you can insert your VBA code.

Having a separate module for your macros keeps your project organized, especially as it grows in complexity.

Step 3: Writing the VBA Code

Now let’s write the actual VBA script to add sheets at the end of your workbook:

Sub AddSheetsAtEnd()
    Dim wsCount As Integer
    Dim i As Integer
    wsCount = Worksheets.Count ‘Count the number of existing worksheets

'Loop to add 5 sheets at the end
For i = 1 To 5
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet" & wsCount + i
Next i


End Sub

• wsCount stores the current number of sheets in your workbook.
• The loop runs 5 times, each time adding a new sheet and naming it sequentially.

Step 4: Running the Macro

After inserting the code:

• Go back to your Excel workbook by pressing Alt + Q.
• From the Excel ribbon, click on Developer tab (you might need to enable this first in Excel Options), then click on Macros.
• Select your AddSheetsAtEnd macro and click Run.

Your workbook should now have 5 additional sheets at the end.

Step 5: Saving Your Workbook with Macros

To ensure your macro-enabled workbook is saved correctly:

• Save your workbook as a Macro-Enabled Workbook (.xlsm).
• Click File > Save As, then in the ‘Save as type’ dropdown, choose Excel Macro-Enabled Workbook.

💡 Note: Macros do not save with regular .xlsx files. Saving as .xlsm is essential for retaining your VBA code.

Automation through VBA can significantly enhance productivity, allowing you to focus on data analysis or other tasks rather than the mechanics of Excel. Now, with just a few lines of code, you can automate the task of adding sheets to the end of your workbook. Whether you're preparing a report or organizing data, these steps provide a quick and efficient solution to streamline your workflow in Excel.

Can this macro add sheets with custom names?

+

Yes, you can modify the VBA code to name the sheets as you like. Instead of using a counter for the name, you can define an array of names or use a specific naming convention within the loop.

Is there a limit to how many sheets can be added at once?

+

Excel allows up to 255 sheets per workbook, but practical performance might dictate fewer sheets. For adding more than 5, just adjust the loop’s range.

How can I make the macro more dynamic for different numbers of sheets?

+

You can pass the number of sheets as an argument to the sub or have a user input form within VBA to ask how many sheets to add.

Will my macros still work if I share this workbook with someone else?

+

Yes, as long as the macro is saved in a macro-enabled workbook (.xlsm) and the recipient has macros enabled, the macros will function as expected.