How to Add Multiple Worksheets to Workbook and Name Them Automatically

I have a fully MS Excel based job. I need to create multiple sheets with a single click and also give the sheets different names. How can I achieve this?

Kartik

Kartik wishes to add multiple worksheets to a workbook and name all the worksheets automatically. If you want complete automation you can use arrays for names but if you wish to keep things flexible so that you can assign names according to your need you can use an Inputbox. Both the methods are described below.

Also if you wish to accomplish all this with a click you’ll need a command button which will be coded accordingly using Excel VBA.

The complete code for the macro attached to the command button is given below:

Option Base 1
Private Sub CommandButton1_Click()
Worksheets.Add after:=Sheet15, Count:=2
Dim p, q As Integer
Dim sheetname

sheetname = Array(“A”, “B”, “C”, “D”, “E”)
p = Worksheets.Count

For q = 1 To p
With Worksheets(q)

‘sheetname = InputBox(“Enter name for worksheet”)
.Name = sheetname(q)
End With
Next q
End Sub

Other parts of the code are also made available:

Sub AddMultiSheetswithNames()
Worksheets.Add After:=Sheet3, Count:=2
Dim p, q As Integer
Dim sheetname As String
p = Worksheets.Count

For q = 1 To p

With Worksheets(q)
sheetname = InputBox(“Enter name for worksheet”)
.Name = sheetname
End With

Next q

End Sub

Option Base 1
Sub AddMultiSheetswithNames()
Worksheets.Add After:=Sheet21, Count:=2
Dim p, q As Integer
Dim sheetname

sheetname = Array(“A”, “B”, “C”, “D”, “E”)

p = Worksheets.Count
For q = 1 To p

With Worksheets(q)
‘sheetname = InputBox(“Enter name for worksheet”)
.Name = sheetname(q)

End With

Next q

End Sub

Watch the video:


Leave a Reply

Your email address will not be published. Required fields are marked *