VBA - How add worksheet with specified name?

[IvoByrtus]

Thank you.

 

[Damon Ostrander]

Hi Ivo,

This will insert a new worksheet after worksheet 1 and name it:

Worksheets.Add(After:=Worksheets(1)).Name = "My New Worksheet"

 

[jeremiah_j2k]

Hi,

what about if you want to add multiple worksheets and the names are coming from a list/range?

Hi Ivo,

This will insert a new worksheet after worksheet 1 and name it:

Worksheets.Add(After:=Worksheets(1)).Name = "My New Worksheet"

 

[Ingolf]

Hi,

what about if you want to add multiple worksheets and the names are coming from a list/range?

Hi,

After the answer to that question, what question comes next?
Formulate the beginning what you want, do not ask question after question!

 

[jeremiah_j2k]

Hi Ingolf,

the code above can rename a single worksheet and its working perfectly fine. however, i'm working on workbook with multiple worksheets that need to be renamed... Do you know a code that can rename sheets based on name list or cell value?

Hi,

After the answer to that question, what question comes next?
Formulate the beginning what you want, do not ask question after question!

 

[Ingolf]

Hi,

Name of sheets to be created, are in column A starting with A2, and the sheet is called List.
Obviously you can change the column or sheet name.

Sub CreateSheets() 
     
    Dim ws As Worksheet 
    Dim Ki As Range 
    Dim ListSh As Range 
     
    With Worksheets("List") 
        Set ListSh = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row) 
    End With 
     
    On Error Resume Next 
    For Each Ki In ListSh 
        If Len(Trim(Ki.Value)) > 0  Then 
            If Len(Worksheets(Ki.Value).Name) = 0 Then 
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Ki.Value 
            End If 
        End If 
    Next Ki 
     
End Sub

 

[jeremiah_j2k]

Thanks Ingolf