How to create multiple worksheets from a list of cell values using VBA

How to create multiple worksheets from a list

In our earlier article, we learned about how to create multiple worksheets in excel without using VBA. In this article, we will be learning How to create multiple worksheets from a list of cell values using VBA.

First, we will create a list of names that we wanted to name the worksheets.

How to create multiple worksheets from a list of cell values using VBA

After making the list we will select the range as our VBA code will work on the selection method.

Sub AddWorksheetsFromSelection()
Dim Ws As Worksheet
Dim Sr As Range
Dim c As Range

Sub AddWorksheetsFromSelection()
    Dim Ws As Worksheet
    Dim Sr As Range
    Dim c As Range

    Set Ws = ActiveSheet
    Set Sr = Selection.Cells
    Application.ScreenUpdating = False

    For Each c In Sr
        sName = Trim(c.Text)
        If Len(sName) > 0 Then
                Worksheets.Add After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = sName
        End If
    Next c

    Ws.Activate
    Application.ScreenUpdating = True
End Sub

We named our VBA module by using Sub AddWorksheetsFromSelection()

Dim Ws As Worksheet –> Used to define worksheet
Dim Sr As Range –> used to define the range for selected item
Dim c As Range –> also used to define range we will read every item from selection

Now we used our declared values to define it.

Set Ws = ActiveSheet –> Ws defined as the active worksheet we are using
Set Sr = Selection.Cells –> Sr is defined as cells selected by us which carry names.

In the below code we first trim the names in the list and defined the list with sName then we get the lenth of this and if it is more than 0 the new work sheet got added and it finds the next value and create a new worksheet till the time they reach the last value in the Sr.

For Each c In Sr
        sName = Trim(c.Text)
        If Len(sName) > 0 Then
                Worksheets.Add After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = sName
        End If
    Next c

In the last part, we can use a Button(Form Control). To get this Button we will go to Developer tab then Controls then Insert drop-down and select Button(Form Control). After adjusting it assign Macro name to it.