Macro to create a new sheet as per Cell values typed in a range

[prkhan56]

Dear All,

I am using Excel 2013 and I would like to have a macro which should create a new sheet when a value is typed in a cell and entered in the range eg A2:A100

If Sales is typed in Cell A2 a new sheet should be created in the workbook with the name Sales, if Expenses is typed in Cell A3 then a new sheet should be created after Sales sheet as Expenses so on and so forth. All new sheets will always be created after the last sheet in the workbook.

If the sheet is existing with the name typed in the Range A2:A100 then the macro should display a message box that sheet already exists!

Thanks in advance

Rashid Khan

 

[mrshl9898]

Hi,

Add this macro to the sheet you want to run it in.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range


    Set KeyCells = Range("A2:A100")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Target.Value
    End With
    
    End If
End Sub

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("A2:A100")) Is Nothing Or Target.Value = "" Then Exit Sub
   If Evaluate("isref('" & Target.Value & "'!A1)") Then
      MsgBox "Sheet " & Target.Value & " already exists"
   Else
      Sheets.Add(, Sheets(Sheets.Count)).Name = Target.Value
   End If
End Sub

 

[prkhan56]

Hello there
Your code does not give a message box if there is a duplicate name
it highlights the following line with run time error

.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Target.Value

Thanks for your time

 

[prkhan56]

Dear Fluff
Thanks for the code.
It works as per my requirement but a small problem if you could fix please

Now when the new sheet is created the cell pointer is in the newly create sheet.

Is it possible to remain in the Active sheet when the new sheet is created.

Thanks for your time and help
Rashid Khan

 

[Fluff]

Simply add

End If
[COLOR=#ff0000]Me.Activate[/COLOR]
End Sub

 

[prkhan56]

Dear Fluff
Excellent!
Thanks for your time and help
Regards

 

[Fluff]

You're welcome & thanks for the feedback