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.
After making the list we will select the range as our VBA code will work on the selection method.
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.