Add multiple sheets and name them automatically

Definition

By using VBA coding you can add new sheets automatically and rename them automatically. This will allow you to even add hundreds of sheet automatically without opening and renaming each and every one of them.

Example

We will explain a simple example of adding 10 worksheets. This will add the worksheets and rename them according to the names you specify. Download the following file for the example:

Make sure your macros are enables, otherwise it will not work.

The following screen will be shown:

Just insert the names of the sheets in column B2 to B11, and click on the "add sheets" button.

The code explained

Sub Button1_Click()
'
' Button1_Click Macro
'

Z = 1 'declare a value for the letter "z", we will need
'this to loop 10 times through our code for adding
'10 sheets
Do While Z < 11 'this loop will run until the variable "Z"
'(which we declared in the previous line) is
'more than 10
Dim WS As Worksheet ' sets variable
Set WS = Sheets.Add ' adds worksheet
Z = Z + 1 ' adds one to our "z" variable
Loop

Sheets("Names").Select ' go to the names sheets

'in the following we declare 20 lines of variables, this
'is our current sheet names, which we added above
'and the new names we wish to change it to
a = Range("a2")
b = Range("a3")
c = Range("a4")
d = Range("a5")
e = Range("a6")
f = Range("a7")
g = Range("a8")
h = Range("a9")
i = Range("a10")
j = Range("a11")
k = Range("b2")
l = Range("b3")
m = Range("b4")
n = Range("b5")
o = Range("b6")
p = Range("b7")
q = Range("b8")
r = Range("b9")
s = Range("b10")
t = Range("b11")

' the next lines changes the sheets to new name

Sheets(a).Name = "k"
Sheets(b).Name = "l"
Sheets(c).Name = "m"
Sheets(d).Name = "n"
Sheets(e).Name = "o"
Sheets(f).Name = "p"
Sheets(g).Name = "q"
Sheets(h).Name = "r"
Sheets(i).Name = "s"
Sheets(j).Name = "t"

End Sub

Using more sheets

You can manipulate the code, to add more sheets. If you cannot do this, contact us, and we can help you.