Solved: Work Sheet Naming

[priya123]

Edit Lucas: Cross posted.

Click here for an explanation of cross-posting

I have a macro that keeps on adding work sheet.I need the sheet to be named say " Ex 1" whenever a sheet is added. Is there a way to do that.. if yes how should I go about that??

[Paleo]

In your macro, after the command to create the sheet, name it, something like this:

[VBA]
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "Ex 1"
[/VBA]

If you want you can put that into a loop to name multiple sheets. If you post your macro will be a little easier to customize it for you.

[priya123]

Thanks... it was very helpful... thanks a ton

[geekgirlau]

Don't forget to mark the thread as "Solved" if you're happy with the result - you can do this via the Thread Tools at the top of the page.

[mdmackillop]

Add this to your ThisWorkbook module to increment sheet names as added.
[vba]Option Explicit
Private Sub Workbook_NewSheet(ByVal sh As Object)
ExSheet sh
End Sub

Sub ExSheet(sh As Worksheet)
Dim Nm1 As Long, Nm2 As Long, Sht As Worksheet
Nm1 = 0
For Each Sht In Worksheets
If Left(Sht.Name, 2) = "Ex" Then
Nm2 = Split(Sht.Name)(1)
If Nm2 > Nm1 Then
Nm1 = Nm2
End If
End If
Next
If Nm1 = 0 Then
sh.Name = "Ex 1"
Else
sh.Name = "Ex " & Nm1 + 1
End If
End Sub
[/vba]

[Brandtrock]

Cross posted HERE

[Charlize]

He's playing with fire. I don't like this behavior . I think I will put a yellow sticky note on my screen with his username.

Charlize

[lucas]

Edit Lucas: Cross posted.

Click here for an explanation of cross-posting

Thanks Brandtrock....