PDA

View Full Version : Solved: Work Sheet Naming



priya123
04-09-2007, 01:44 PM
Edit Lucas: Cross posted (http://www.ozgrid.com/forum/showthread.php?t=66969).

Click here (http://www.excelguru.ca/node/7) 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
04-09-2007, 04:05 PM
In your macro, after the command to create the sheet, name it, something like this:


ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "Ex 1"


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
04-09-2007, 04:11 PM
Thanks... it was very helpful... thanks a ton

geekgirlau
04-09-2007, 05:16 PM
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
04-09-2007, 05:52 PM
Add this to your ThisWorkbook module to increment sheet names as added.
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

Brandtrock
04-10-2007, 01:42 AM
Cross posted HERE (http://www.ozgrid.com/forum/showthread.php?p=347097#post347097)

Charlize
04-10-2007, 05:31 AM
He's playing with fire. I don't like this behavior :angry2: . I think I will put a yellow sticky note on my screen with his username.

Charlize

lucas
04-10-2007, 06:56 AM
Edit Lucas: Cross posted (http://www.ozgrid.com/forum/showthread.php?t=66969).

Click here (http://www.excelguru.ca/node/7) for an explanation of cross-posting

Thanks Brandtrock....