Create Sheet Names from a List in Excel

Excel is a robust spreadsheet programme that provides a wealth of functions to aid users in effectively organising and analysing their data. The option to generate several sheets within a single spreadsheet is one such capability. Although Excel offers standard sheet names like Sheet1, Sheet2, and so on, it's frequently advantageous to give these sheets names that are more relevant and descriptive.

In this tutorial, we'll look at a quick but efficient way to name specific Excel sheets using a list of values. The usefulness and accessibility of your Excel workbooks can be substantially improved by having clearly defined sheet titles, whether you're working on a personal project, leading a team, or handling complicated data sets. You will have the knowledge and abilities necessary to dynamically generate sheet names based on your needs by the end of this session. So let's get started and learn how to customise sheet names in Excel using a list!

Creating Sheet Names from a List in Excel

Here we will first create a VBA module and then select the range of cells to complete the task. So let us see a simple process to learn how you can create sheet names from a list in Excel.

Step 1

Consider an Excel sheet where you have a list of names, as shown in the following screenshot:

First, use Alt + F11 to open the VBA application.

Step 2

Then, click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Example

Sub CreateSheetsFromAList()
    Dim Rg As Range
    Dim Rg1 As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set Rg = Application.InputBox("Select a range:", "Create Sheets List", , , , , , 8)
    If Rg Is Nothing Then Exit Sub
    For Each Rg1 In Rg
        If Rg1 <> "" Then
            Call Sheets.Add(, Sheets(Sheets.Count))
            Sheets(Sheets.Count).Name = Rg1.Value
        End If
    Next
End Sub

Step 3

Then click F5 to run the module, select the range of cells, and click OK to complete the task.

F5 > Select cells > Ok.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create sheet names from a list in Excel to highlight a particular set of data.