VBA Create Multiple Worksheets From A List Of Cell Values

[FETulsa]

I tried using the below VBA code, it is creating "nameless" worksheets, so sheet1 , 2 , 3 and so on, rather than using the value in the cell as the sheet's name. Any tips to resolve this issue?

Sub AddSheets()

Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("A1:A7")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Do those sheets already exist? Do the values in A1:A7 contain characters that are forbidden in sheet names?

 

[FETulsa]

Do those sheets already exist? Do the values in A1:A7 contain characters that are forbidden in sheet names?

OMG! You are a genius JoeMo!!! What a simple fix. Yes the cell names had slashes and dashes -/. I so did not want to do the whole copy paste thing, I had (49) new sheets I wanted to create. Thank you! Thank you! Thank you!

 

[JoeMo]

OMG! You are a genius JoeMo!!! What a simple fix. Yes the cell names had slashes and dashes -/. I so did not want to do the whole copy paste thing, I had (49) new sheets I wanted to create. Thank you! Thank you! Thank you!

You are welcome - thanks for the reply.