Hi Guys,

I am having problem. I am able to create new sheets having name as per cell values but I am unable to do that if I add new value at last and run the script. Below is the script that I am working

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("C14:C30")

If Not IsEmpty(xRg.Value) Then

With wBk

Worksheets("S1").Copy after:=.Sheets(.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = xRg.Value
On Error GoTo 10
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"

End If
On Error GoTo 0
End With

End If

Next xRg