Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
Add a space after #
sheetnumber = Val(Trim(Mid(sheetname, InStr(sheetname, "# ") + 1))) sheetnumber = sheetnumber + 1 sheetprefix = Trim(Left(sheetname, InStr(sheetname, "# "))) Roger "Sean" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
Have you tried:
ActiveSheet.Name = sheetprefix & " " & sheetnumber Regards Trevor "Sean" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
I tried that, but made no difference
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
I think that should work, because it is outside of his trim, which is why my
suggestion made no difference for him, I think. "Trevor Shuttleworth" wrote: Have you tried: ActiveSheet.Name = sheetprefix & " " & sheetnumber Regards Trevor "Sean" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
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 wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
Thanks Guys, Trevor your one was the simplest for me to put in and
works a treat |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
Sean
Perhaps, something like: sheetnumber = sheetnumber + 1 If sheetnumber 20 Then Exit Sub Regards Trevor "Sean" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
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" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a Sheet Q
Thanks Chip
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i rename a sheet if the rename tab is inactive? | Excel Worksheet Functions | |||
macro to: Add new sheet, then rename new sheet with todays date | Excel Worksheet Functions | |||
Move data to new sheet - rename sheet based on criteria ? | Excel Discussion (Misc queries) | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
Button to copy sheet, rename sheet sequencially. | Excel Programming |