create and rename Excel sheet as previous name +1

[m_vishal_c]

HI
How can i create and rename excel sheet in same workbook as per previous name +1
for example when we click on + sign excel add "Sheet2", "Sheet3" and so on
but what about sheet name is "abc1". Now i press + then excel should create sheet "abc2","abc3"
through vba

heaps thanks in advance

thanks

 

[ZVI]

Hi,
Put the below code into ThisWorkbook module

' Put this code into ThisWorkbook module of the workbook
 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
 
  Dim i As Long, OldName As String, NewName As String
 
  ' Create NewName
  OldName = Worksheets(Worksheets.Count - 1).Name
  For i = Len(OldName) To 1 Step -1
    If Not Mid(OldName, i, 1) Like "#" Then Exit For
  Next
  If i = 0 Then
    NewName = OldName & "1"
  Else
    NewName = Left(OldName, i) & Val(Mid(OldName, i + 1)) + 1
  End If
 
  ' Rename the created sheet if there is no sheet with the same name as NewName
  On Error Resume Next
  Sh.Name = NewName
 
End Sub

Regards

 

[m_vishal_c]

hi thanks for replying.
this code does not create new sheet. and name does not get changed.

for example :- sheet name is "abc1", when i click on + sign new sheet will be created with "abc2" name. but does not work

 

[footoo]

Do you have macros enabled?

What do want to do if if you have sheets named ABC1, ABC2 and ABC4?
Should the next sheet be ABC3 or ABC5?
The following code will create ABC5 (the sheets can be in any order):

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet, x, y%, i%
For Each ws In Worksheets
    If Left(ws.Name, 3) = "ABC" Then
        x = Replace(ws.Name, "ABC", "")
        If IsNumeric(x) Then If x + 0 >= y Then y = x + 1
    End If
Next
If y <> 0 Then ActiveSheet.Name = "ABC" & y
End Sub

 

[ZVI]

hi thanks for replying.
this code does not create new sheet. and name does not get changed.

Then you hit + button in a sheets tab field to create new sheet then the code automatically renames the created sheet using name of the previous (last) sheet with incrementing its number

 

[m_vishal_c]

Then you hit + button in a sheets tab field to create new sheet then the code automatically renames the created sheet using name of the previous (last) sheet with incrementing its number

hi Thanks for replying

i am new to vba, i have just placed this above code to Module1 in same workbook but i can not see Workbook_NewSheet name in macro. am i doing something wrong?

 

[ZVI]

hi Thanks for replying

i am new to vba, i have just placed this above code to Module1 in same workbook but i can not see Workbook_NewSheet name in macro. am i doing something wrong?

As it was mentioned in the post 2 the code should go into ThisWorkbook module, not to the Module1.
In a localized (not English) version of Excel a ThisWorkbook module has the name in local language, but it is always before the Module1. More exact - find it last in Microsoft Excel Objects part where sheets modules are stored, not in Modules part where Module1 is stored.