Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Private Sub cmdAdd_Click()
Sheets("Sheet1").Select Sheets.Add End Sub The above code will create a copy of Sheet1 by increments of 1. I want to modify the code and name the sheet based on a value in A1. For example; A1 = Joe1 when the code is run it names the sheet Joe2 run the code again it names it Joe3 etc Change A1 to Mary1 when the code is run it names the sheet Mary2 run the code again it names it Mary3 etc Change A1 to ()1 etc If Joe1 is in A1 again when the code is run it names the sheet Joe4 Anyone know if this is possible? Thanks Pat |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Pat,
Try this code Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName .Value = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... Private Sub cmdAdd_Click() Sheets("Sheet1").Select Sheets.Add End Sub The above code will create a copy of Sheet1 by increments of 1. I want to modify the code and name the sheet based on a value in A1. For example; A1 = Joe1 when the code is run it names the sheet Joe2 run the code again it names it Joe3 etc Change A1 to Mary1 when the code is run it names the sheet Mary2 run the code again it names it Mary3 etc Change A1 to ()1 etc If Joe1 is in A1 again when the code is run it names the sheet Joe4 Anyone know if this is possible? Thanks Pat |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
That's pretty good Phil.
The only change (if possible) I would need is to prevent ()1 changing to ()2 in the cell, this increments to reflect the sheet change. The problem I can see happening is that after trying to locate the last sheet number for a particular person the wrong number is entered and the code will fail. I have tried this out and the code seemed to go into a loop which I then had to use ctrl+alt+del to get out of. Regards Pat "Bob Phillips" wrote in message ... Pat, Try this code Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName .Value = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... Private Sub cmdAdd_Click() Sheets("Sheet1").Select Sheets.Add End Sub The above code will create a copy of Sheet1 by increments of 1. I want to modify the code and name the sheet based on a value in A1. For example; A1 = Joe1 when the code is run it names the sheet Joe2 run the code again it names it Joe3 etc Change A1 to Mary1 when the code is run it names the sheet Mary2 run the code again it names it Mary3 etc Change A1 to ()1 etc If Joe1 is in A1 again when the code is run it names the sheet Joe4 Anyone know if this is possible? Thanks Pat |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Apologizes Bob I incorrectly addressed you by another name.
"Pat" wrote in message ... That's pretty good Phil. The only change (if possible) I would need is to prevent ()1 changing to ()2 in the cell, this increments to reflect the sheet change. The problem I can see happening is that after trying to locate the last sheet number for a particular person the wrong number is entered and the code will fail. I have tried this out and the code seemed to go into a loop which I then had to use ctrl+alt+del to get out of. Regards Pat "Bob Phillips" wrote in message ... Pat, Try this code Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName .Value = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... Private Sub cmdAdd_Click() Sheets("Sheet1").Select Sheets.Add End Sub The above code will create a copy of Sheet1 by increments of 1. I want to modify the code and name the sheet based on a value in A1. For example; A1 = Joe1 when the code is run it names the sheet Joe2 run the code again it names it Joe3 etc Change A1 to Mary1 when the code is run it names the sheet Mary2 run the code again it names it Mary3 etc Change A1 to ()1 etc If Joe1 is in A1 again when the code is run it names the sheet Joe4 Anyone know if this is possible? Thanks Pat |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Pat,
The problem with that is that it will cycle through many sheets to see if they exist, but I did allow for this, so all you need to do is remove the line .Value = sName which saves it back. Amended code. Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... That's pretty good Phil. The only change (if possible) I would need is to prevent ()1 changing to ()2 in the cell, this increments to reflect the sheet change. The problem I can see happening is that after trying to locate the last sheet number for a particular person the wrong number is entered and the code will fail. I have tried this out and the code seemed to go into a loop which I then had to use ctrl+alt+del to get out of. Regards Pat |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Hello again Bob,
.Value = sName Although removing the above has aloud the origional value to be reinstated, but if the code is run again the same problem (as I mentioned earlier) with the continues looping keeps occurs. Pat "Bob Phillips" wrote in message ... Pat, The problem with that is that it will cycle through many sheets to see if they exist, but I did allow for this, so all you need to do is remove the line .Value = sName which saves it back. Amended code. Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... That's pretty good Phil. The only change (if possible) I would need is to prevent ()1 changing to ()2 in the cell, this increments to reflect the sheet change. The problem I can see happening is that after trying to locate the last sheet number for a particular person the wrong number is entered and the code will fail. I have tried this out and the code seemed to go into a loop which I then had to use ctrl+alt+del to get out of. Regards Pat |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Pat,
What a dodo (me!) :-) Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then sName = .Value Do sName = Left(sName, Len(sName) - 1) & _ Right(sName, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... Hello again Bob, .Value = sName Although removing the above has aloud the origional value to be reinstated, but if the code is run again the same problem (as I mentioned earlier) with the continues looping keeps occurs. Pat "Bob Phillips" wrote in message ... Pat, The problem with that is that it will cycle through many sheets to see if they exist, but I did allow for this, so all you need to do is remove the line .Value = sName which saves it back. Amended code. Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... That's pretty good Phil. The only change (if possible) I would need is to prevent ()1 changing to ()2 in the cell, this increments to reflect the sheet change. The problem I can see happening is that after trying to locate the last sheet number for a particular person the wrong number is entered and the code will fail. I have tried this out and the code seemed to go into a loop which I then had to use ctrl+alt+del to get out of. Regards Pat |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet naming
Eureka!
Thanks ever so much Bob. Cheers Pat "Bob Phillips" wrote in message ... Pat, What a dodo (me!) :-) Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then sName = .Value Do sName = Left(sName, Len(sName) - 1) & _ Right(sName, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... Hello again Bob, .Value = sName Although removing the above has aloud the origional value to be reinstated, but if the code is run again the same problem (as I mentioned earlier) with the continues looping keeps occurs. Pat "Bob Phillips" wrote in message ... Pat, The problem with that is that it will cycle through many sheets to see if they exist, but I did allow for this, so all you need to do is remove the line .Value = sName which saves it back. Amended code. Sub NameSheet() Dim fExists As Boolean Dim sName As String Dim sh As Worksheet Set sh = ActiveSheet With sh.Range("A1") If IsNumeric(Right(.Value, 1)) Then Do sName = Left(.Value, Len(.Value) - 1) & _ Right(.Value, 1) + 1 fExists = SheetExists(sName) Loop Until Not fExists Worksheets.Add.Name = sName sh.Activate End If End With End Sub '----------------------------------------------------------------- Function SheetExists(sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Pat" wrote in message ... That's pretty good Phil. The only change (if possible) I would need is to prevent ()1 changing to ()2 in the cell, this increments to reflect the sheet change. The problem I can see happening is that after trying to locate the last sheet number for a particular person the wrong number is entered and the code will fail. I have tried this out and the code seemed to go into a loop which I then had to use ctrl+alt+del to get out of. Regards Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
naming a sheet the same as a cell in that sheet | Excel Discussion (Misc queries) | |||
Naming a sheet | Excel Discussion (Misc queries) | |||
naming sheet tab | Excel Worksheet Functions | |||
Naming Sheet | Excel Discussion (Misc queries) | |||
Naming a new sheet. | Excel Programming |