Rename a Sheet Q

[Sean]

Routine below does a couple of things but the line I'm trying to
tweak, is after it creates a new sheet with the sheet number, it names
it for example xxxx #13, how can I get it to rename as xxxx # 13. In
other words a space between the number and the #

Thanks


Sub copysheet()
Dim i As Integer

Application.ScreenUpdating = False
i = Sheets.Count
Sheets(i).Select


sheetname = ActiveSheet.Name
sheetnumber = Val(Trim(Mid(sheetname, InStr(sheetname, "#") + 1)))
sheetnumber = sheetnumber + 1
sheetprefix = Trim(Left(sheetname, InStr(sheetname, "#")))
ActiveSheet.Copy after:=Sheets(ActiveSheet.Index)
ActiveSheet.Name = sheetprefix & sheetnumber
If sheetnumber Mod 2 = 0 Then
ActiveSheet.Tab.ColorIndex = 6
Else
ActiveSheet.Tab.ColorIndex = 41
End If
ActiveWindow.Zoom = 75
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False


End Sub

 

[Roger Converse]

Add a space after #

sheetnumber = Val(Trim(Mid(sheetname, InStr(sheetname, "# ") + 1)))
sheetnumber = sheetnumber + 1
sheetprefix = Trim(Left(sheetname, InStr(sheetname, "# ")))

Roger

 

[Trevor Shuttleworth]

Have you tried:

ActiveSheet.Name = sheetprefix & " " & sheetnumber

Regards

Trevor

 

[Sean]

I tried that, but made no difference

 

[Roger Converse]

I think that should work, because it is outside of his trim, which is why my
suggestion made no difference for him, I think.

 

[Dave Peterson]

Dim Wks as worksheet
set wks = activesheet
with wks
.name = replace(.name,"#", "# ")
end with

Use
.name = application.substitute(.name,"#","# ")
if you're using xl97.

 

[Sean]

Thanks Guys, Trevor your one was the simplest for me to put in and
works a treat

 

[Sean]

One final question on a tweak to this, how could I limit the insertion
of a max of 20 sheets in workbook, and if user tries to create a 21st
a message box would appear saying "max employees already created"?



Sub CreateNewSheet()
Dim i As Integer

Application.ScreenUpdating = False
i = Sheets.Count
Sheets(i).Select


sheetname = ActiveSheet.Name
sheetnumber = Val(Trim(Mid(sheetname, InStr(sheetname, "# ") + 1)))
sheetnumber = sheetnumber + 1
sheetprefix = Trim(Left(sheetname, InStr(sheetname, "# ")))
ActiveSheet.Copy after:=Sheets(ActiveSheet.Index)
ActiveSheet.Name = sheetprefix & " " & sheetnumber
If sheetnumber Mod 2 = 0 Then
ActiveSheet.Tab.ColorIndex = 6
Else
ActiveSheet.Tab.ColorIndex = 41
End If
ActiveWindow.Zoom = 75
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False

ClearCells
End Sub

 

[Trevor Shuttleworth]

Sean

Perhaps, something like:

sheetnumber = sheetnumber + 1
If sheetnumber > 20 Then Exit Sub

Regards

Trevor

 

[Sean]

I was thinking more of this, but I can't get it to run

i = Sheets.Count

If i = Sheets.Count >= 22 Then
MsgBox "You have reached the maximum number of employees that you can
record, please contact xyz"
Exit Sub
End If

 

[Chip Pearson]

If i = Sheets.Count >= 22 Then

Of course, you want one or the other, not both.

If i >= 22 Then

or

If Sheets.Count >= 22 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Sean]

Thanks Chip