Insert and Name Sheets from Cell Values

In this post, I’ll share a short snippet of VBA code that creates new worksheets based on the names stored in cell values. This can be helpful when you have a list of departments, and want to create one worksheet for each department. Or a list of accounts, or employees, or regions, and so on. This post is the result of an anonymous question I received. So, to whoever asked it … thank you for your question and I hope this post helps!

Objective

Before we get too far, let’s take a look at our objective. We have some cell values in an ordinary range, like this:

We’d like to be able to select the range and have Excel create and name a new sheet for each item in our range.

Since I’m unaware of any built-in way to accomplish this in the version of Excel I’m using as I write this, we’ll turn to VBA.

VBA

VBA (Visual Basic for Applications) is a programming language we can use to write macros. Now, the good news is I’ve already written the code so you can just swipe it.

If you aren’t concerned with understanding how the macro works, just jump to to the end of the post and download the Excel file (which contains the VBA code).

But, if you’d like to analyze and understand the code, here’s a quick run down.

Code Snippet

Let’s start by taking a look at the code:

Sub Insert_Sheet_Names()
  For Each c In Selection
    With Sheets.Add(After:=ActiveSheet)
      .Name = c.Value
    End With
  Next c
End Sub

Now let’s talk through it.

• The Sub and related End Sub lines define the procedure name (Insert_Sheet_Names) and the body of the code.
• The For Each and related Next lines define a collection loop. A loop is a code segment that can be repeated a number of times. In our case, it will be repeated once for each cell (c) in the currently selected range (Selection).
• The With and related End With lines tell Excel to Add a new worksheet after the active sheet, and then name it with the value from the cell.

How to Use

One possible way to use this code is to follow these steps:

• Download and extract the zip file below
• Open the InsertSheets.xlsm macro-enabled workbook whenever you need to perform this task
• Select a range of cells on ANY open workbook
• Then run the macro (View > Macros > View Macros or Alt+F8)

Note: the macro can operate on any open workbook, and there is no need to copy the VBA code to the workbook that contains the range of sheet names.

As with anything in Excel, there are other ways to use this code, including adding it to your personal macro workbook.

Conclusion

VBA is a big language, and there are many other code snippets that will accomplish the same thing. Plus, there are many ways to improve the code to handle errors, for example, if the value in the cell doesn’t meet the specifications for a sheet name, or if the current selection isn’t a range of cells and instead is a chart or other object. If you have any alternatives or improvements to the code, please share by posting a comment below … thanks!

Sample file: InsertSheets.zip

• Note: due to security reasons, the macro-enabled workbook has been zipped into a zip folder. To use, download the zip file, and extract the InsertSheets.xlsm file.