How to Add a New Worksheet in Excel Automatically
The manual way to add a new worksheet in Excel may be quite easy and fast, but sometimes you may want to add a new worksheet automatically, for example by using VBA code, or by copying worksheets from another workbook. Here are some ways to add a new worksheet in Excel automatically:
1] Using VBA Code
This method is the most advanced and powerful way to add a new worksheet in Excel because you can write code that can perform a wide variety of tasks and functions. You just need to open the “Visual Basic Editor” window by pressing the “Alt + F11” keys on the keyboard, and then add a new module by clicking the Insert menu, and selecting the “Module” option. There, you can write a VBA code which can add a new worksheet in Excel in different ways. Here is an example of a VBA code that can add a new worksheet in Excel, and name it according to the current date:
Sub AddNewSheet()
Dim ws As Worksheet
Set ws = Worksheets.Add
Ws. Name = Format(Date, “dd-mm-yyyy” )
End Sub
To run this VBA code, you can press the F5 key on the keyboard, click the “Run” menu, and select the “Run Sub/UserForm” option. Excel will add a new worksheet on the left of the active worksheet, and name it according to the current date, as shown in the following screenshot. You can change this VBA code to suit your needs, for example by specifying the position, number, or worksheet name you want to add.
2] Using the Move or Copy Feature
This way is an easier and practical way to add a new worksheet in Excel, especially if you want to copy a worksheet from another workbook, or another place in the same workbook. You just need to right-click on the worksheet tab you want to copy and select the “Move or Copy” option from the popping-out context menu, as shown in the following screenshot.
Then, you will see a “Move or Copy” dialog box that displays several options, such as “To book”, “Before sheet”, and “Create a copy”. Select the destination workbook and worksheet that you want to copy your worksheet too, and check the option “Create a copy”. Click OK.
Excel will add a new worksheet in the workbook and position you selected, and give it the same name as the original worksheet, plus parentheses with numbers inside, such as Sheet1 (2), Sheet1 (3), and so on. You can rename a new worksheet by double-clicking on its tab and typing the name you want.
Tips and Tricks to Add New Worksheet in Excel
Now you already know how to add a new worksheet in Excel with various methods, both manual and automatic. However, there are some tips and tricks that can help you save time and increase your productivity when working with worksheets in Excel. Here are some tips and tricks you can try:
1] Using Keyboard Shortcuts
You can add a new worksheet in Excel quickly and easily by using keyboard shortcuts. You just need to press the Shift + F11 keys on the keyboard, and Excel will add a new worksheet on the left of the active worksheet, and give it default names such as Sheet1, Sheet2, and so on.
You can rename a new worksheet by double-clicking on its tab and typing the name you want. This keyboard shortcut works in all versions of Excel and can help you save time and mouse clicks.