How to Create New Workbook with Specific Number of Worksheets

Numerous tools are available in Excel, a popular spreadsheet programme, to aid in effectively organising and analysing data. Making workbooks, which are collections of worksheets where data may be entered, altered, and presented, is a fundamental component of using Excel.

We will walk you through the steps of establishing a new Excel workbook with a certain number of worksheets in this article. This article will give you the skills you need to set up a workbook with several pages, whether you need to do so for data recording, financial analysis, or any other reason. No of your level of experience, this guide is made to be user-friendly for beginners and includes clear directions and pertinent screenshots to make learning a snap. Let's get started and discover how to make a new workbook with the precise number of worksheets you need!

Create New Workbook With Specific Number of Worksheets

Here we will first create a VBA module, then run it and specify the number of required worksheets to complete the task. So let us see a simple process to learn how you can create a new workbook with a specific number of worksheets in Excel.

Step 1

Consider any Excel sheet. First, use Alt + F11 to open the VBA application.

Step 2

Then, click on "insert" and select "module" and copy the following code into the textbox.

Insert > Module > Copy

Example

Sub MakeWorkbook()
   Dim xOldCount As Integer
   Dim xNewCount As Variant
   xOldCount = Application.SheetsInNewWorkbook
   xNewCount = Application.InputBox("How many sheets in the new workbook do you want?", "Create New Workbook", , , , , , 1)
   If xNewCount = False Then Exit Sub
   If (xNewCount < 1) Or (CLng(xNewCount) > 255) Then
      MsgBox "The number must between 1 and 255!"
      Exit Sub
   End If
   With Application
      .SheetsInNewWorkbook = xNewCount
      .Workbooks.Add
      .SheetsInNewWorkbook = xNewCount
   End With
End Sub

Step 3

Now click F5 to run the module, enter the number of sheets required, and click OK to complete the task.

This is how you can create a new workbook with a specific number of worksheets in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a new workbook with a specific number of worksheets in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 12-Jul-2023

99 Views

Kickstart Your Career

Get certified by completing the course

Get Started