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....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.