Create a new sheets based upon the value in the range and gotot next cell if sheet exists

[pm.patel189]

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

[AlphaFrog]

I am unable to do that if I add new value at last

I don't understand what that means; "add new value at last".

[pm.patel189]

Here is example:

I have Company1 in C14 cell and Company2 in C15 cell and when I run this script then this creates two new sheets having name Company1 and Company2. But when I add Company3 in C16 Cell and run the script then,

It creates 3 new sheet S1(1), S1(2), Company3. I dont want that, instead it should create just 1 new sheet Company3

[pm.patel189]

I don't understand what that means; "add new value at last".

Here is example:

I have Company1 in C14 cell and Company2 in C15 cell and when I run this script then this creates two new sheets having name Company1 and Company2. But when I add Company3 in C16 Cell and run the script then,

It creates 3 new sheet S1(1), S1(2), Company3. I dont want that, instead it should create just 1 new sheet Company3

[AlphaFrog]

Please Login or Register  to view this content.

[pm.patel189]

That worked...!!! Thanks AlphaFrog