VBA: get name of the 3rd worksheet and then split it

[dl7631]

Hello!

I have several worksheets on one file. The third worksheet is called <letter P + some number>, e.g. 'P27' or 'P138'. The first two sheets have very different names.

What would be the VBA code to:

1. Add a new THIRD worksheet - i.e., right before the current third sheet
2. Name it <P + number that is the number of the current 3rd sheet + 1>, e.g., if the current 3rd sheet is 'P27', then the new 3rd sheet should be called 'P28'.

Thank you so much!

 

[Fluff]

How about

Sub AddSht()

   Dim ShtNme As String
   With Sheets(3)
      ShtNme = Left(.Name, 1) & Right(.Name, Len(.Name) - 1) + 1
   End With
   Sheets.Add(Sheets(3)).Name = ShtNme
   
End Sub

 

[dl7631]

Thank you so much, it works perfectly!

 

[Fluff]

Glad to help & thanks for the feedback