Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
hi,
I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Option Explicit
Sub testme() Dim iCtr As Long Dim wks As Worksheet Dim myStr As String Set wks = Worksheets.Add iCtr = -1 Do iCtr = iCtr + 1 If iCtr = 0 Then myStr = "" Else myStr = Format(iCtr, "0") End If On Error Resume Next wks.Name = "Database" & myStr If Err.Number = 0 Then 'it worked Exit Do Else Err.Clear 'keep looking, stay in loop End If Loop MsgBox wks.Name End Sub You may want to consider naming your sheets Database001, database002, ... If you ever decide to sort those sheets, they'll be easy to sort. This line: myStr = Format(iCtr, "0") would become myStr = Format(iCtr, "000") And if you ever want to sort the sheets... Chip Pearson's code: http://www.cpearson.com/excel/sortws.htm David McRitchie's code: http://www.mvps.org/dmcritchie/excel...#sortallsheets Jac wrote: hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Add an "on error goto 0" after the Loop statement:
Loop On error goto 0 msgbox wks.name end sub Dave Peterson wrote: Option Explicit Sub testme() Dim iCtr As Long Dim wks As Worksheet Dim myStr As String Set wks = Worksheets.Add iCtr = -1 Do iCtr = iCtr + 1 If iCtr = 0 Then myStr = "" Else myStr = Format(iCtr, "0") End If On Error Resume Next wks.Name = "Database" & myStr If Err.Number = 0 Then 'it worked Exit Do Else Err.Clear 'keep looking, stay in loop End If Loop MsgBox wks.Name End Sub You may want to consider naming your sheets Database001, database002, ... If you ever decide to sort those sheets, they'll be easy to sort. This line: myStr = Format(iCtr, "0") would become myStr = Format(iCtr, "000") And if you ever want to sort the sheets... Chip Pearson's code: http://www.cpearson.com/excel/sortws.htm David McRitchie's code: http://www.mvps.org/dmcritchie/excel...#sortallsheets Jac wrote: hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Hi JAC
Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
typo due to testing
If UCase(Left(ws.Name, 2)) = "DATABASE" Then If UCase(Left(ws.Name, 8)) = "DATABASE" Then -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... One way to try Sub addsheet() For Each ws In Worksheets If UCase(Left(ws.Name, 2)) = "DATABASE" Then For i = 1 To Len(ws.Name) If Mid(ws.Name, i, 1) Like "*[0-9]*" Then _ Exit For Next i ms = Right(ws.Name, Len(ws.Name) - 1 - 1) If ms mn Then mn = ms End If Next ws Sheets.Add ActiveSheet.Name = "Database" & mn + 1 End Sub -- Don Guillett SalesAid Software "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Is there a reason you stop after 255 sheets?
I don't think I've approached that number, but excel will allow more than that -- if your pc can handle it. Joergen Bondesen wrote: Hi JAC Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Hi Dave
Thanks. I will ajuste my sampel. I do not recall where I have than number from. Older version of Excel perhaps? -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... Is there a reason you stop after 255 sheets? I don't think I've approached that number, but excel will allow more than that -- if your pc can handle it. Joergen Bondesen wrote: Hi JAC Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Glad to help.
-- Don Guillett SalesAid Software "Jac" wrote in message ... Thanks, Don! "Don Guillett" wrote: One way to try Sub addsheet() For Each ws In Worksheets If UCase(Left(ws.Name, 2)) = "DATABASE" Then For i = 1 To Len(ws.Name) If Mid(ws.Name, i, 1) Like "*[0-9]*" Then _ Exit For Next i ms = Right(ws.Name, Len(ws.Name) - 1 - 1) If ms mn Then mn = ms End If Next ws Sheets.Add ActiveSheet.Name = "Database" & mn + 1 End Sub -- Don Guillett SalesAid Software "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
As far as I can remember, the number of sheets has been limited by your pc's
resources. But I bet you're thinking of the tools|options|General tab|sheets in new workbook. That dialog has a limit of 255. Joergen Bondesen wrote: Hi Dave Thanks. I will ajuste my sampel. I do not recall where I have than number from. Older version of Excel perhaps? -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... Is there a reason you stop after 255 sheets? I don't think I've approached that number, but excel will allow more than that -- if your pc can handle it. Joergen Bondesen wrote: Hi JAC Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Hi Dave
I will not bet with you, because I will lose. 8-) This means I can have a sheet for each day in a year (in a file) and this is very good news for me. I am really glad you took your time for helping me out of my delusion. -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... As far as I can remember, the number of sheets has been limited by your pc's resources. But I bet you're thinking of the tools|options|General tab|sheets in new workbook. That dialog has a limit of 255. Joergen Bondesen wrote: Hi Dave Thanks. I will ajuste my sampel. I do not recall where I have than number from. Older version of Excel perhaps? -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... Is there a reason you stop after 255 sheets? I don't think I've approached that number, but excel will allow more than that -- if your pc can handle it. Joergen Bondesen wrote: Hi JAC Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Hi Don
I am not able to run your macro. I think this line ms = Right(ws.Name, Len(ws.Name) - 1 - 1) must be changed to ms = Right(ws.Name, Len(ws.Name) - i + 1) I do hope you agree with me. -- Best regards Joergen Bondesen "Don Guillett" wrote in message ... typo due to testing If UCase(Left(ws.Name, 2)) = "DATABASE" Then If UCase(Left(ws.Name, 8)) = "DATABASE" Then -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... One way to try Sub addsheet() For Each ws In Worksheets If UCase(Left(ws.Name, 2)) = "DATABASE" Then For i = 1 To Len(ws.Name) If Mid(ws.Name, i, 1) Like "*[0-9]*" Then _ Exit For Next i ms = Right(ws.Name, Len(ws.Name) - 1 - 1) If ms mn Then mn = ms End If Next ws Sheets.Add ActiveSheet.Name = "Database" & mn + 1 End Sub -- Don Guillett SalesAid Software "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
I tested this with DB1, DB22, etc and it worked just fine, as written. Perhaps you have Database 22 instead. I always try to make sheet names short and without spaces. The +1 comes in when the next sheet becomes Db23, etc. Sub addsheet() For Each ws In Worksheets If UCase(Left(ws.Name, 2)) = "DB" Then For i = 1 To Len(ws.Name) If Mid(ws.Name, i, 1) Like "*[0-9]*" Then _ Exit For Next i ms = Right(ws.Name, Len(ws.Name) - 1 - 1) If ms mn Then mn = ms End If Next ws Sheets.Add ActiveSheet.Name = "Db" & mn + 1 End Sub -- Don Guillett SalesAid Software "Joergen Bondesen" wrote in message ... Hi Don I am not able to run your macro. I think this line ms = Right(ws.Name, Len(ws.Name) - 1 - 1) must be changed to ms = Right(ws.Name, Len(ws.Name) - i + 1) I do hope you agree with me. -- Best regards Joergen Bondesen "Don Guillett" wrote in message ... typo due to testing If UCase(Left(ws.Name, 2)) = "DATABASE" Then If UCase(Left(ws.Name, 8)) = "DATABASE" Then -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... One way to try Sub addsheet() For Each ws In Worksheets If UCase(Left(ws.Name, 2)) = "DATABASE" Then For i = 1 To Len(ws.Name) If Mid(ws.Name, i, 1) Like "*[0-9]*" Then _ Exit For Next i ms = Right(ws.Name, Len(ws.Name) - 1 - 1) If ms mn Then mn = ms End If Next ws Sheets.Add ActiveSheet.Name = "Database" & mn + 1 End Sub -- Don Guillett SalesAid Software "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
I think Don makes an excellent point. I find that it's usually easier to split
up data (to create report worksheets) than it is to combine a bunch of sheets to get consolidated reports. Joergen Bondesen wrote: Hi Dave I will not bet with you, because I will lose. 8-) This means I can have a sheet for each day in a year (in a file) and this is very good news for me. I am really glad you took your time for helping me out of my delusion. -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... As far as I can remember, the number of sheets has been limited by your pc's resources. But I bet you're thinking of the tools|options|General tab|sheets in new workbook. That dialog has a limit of 255. Joergen Bondesen wrote: Hi Dave Thanks. I will ajuste my sampel. I do not recall where I have than number from. Older version of Excel perhaps? -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... Is there a reason you stop after 255 sheets? I don't think I've approached that number, but excel will allow more than that -- if your pc can handle it. Joergen Bondesen wrote: Hi JAC Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name Sheets in Series
Hi Don and Dave.
Don, I rearly appreciate the excel-file you mailed to me, thanks. You are both quit rigth, that one sheets is best for using filter, pivot etc. but when the user WANTS 1 sheet / day, then it is my destiny to deliver the demand. 8-) -- Best regards Joergen Bondesen "Don Guillett" wrote in message ... Going back to the original need. Why do you need a sheet for each day of the year. Why not have only ONE sheet with ALL days and just use datafilterautofilter for what you need? -- Don Guillett SalesAid Software "Joergen Bondesen" wrote in message ... Hi Dave I will not bet with you, because I will lose. 8-) This means I can have a sheet for each day in a year (in a file) and this is very good news for me. I am really glad you took your time for helping me out of my delusion. -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... As far as I can remember, the number of sheets has been limited by your pc's resources. But I bet you're thinking of the tools|options|General tab|sheets in new workbook. That dialog has a limit of 255. Joergen Bondesen wrote: Hi Dave Thanks. I will ajuste my sampel. I do not recall where I have than number from. Older version of Excel perhaps? -- Best regards Joergen Bondesen "Dave Peterson" wrote in message ... Is there a reason you stop after 255 sheets? I don't think I've approached that number, but excel will allow more than that -- if your pc can handle it. Joergen Bondesen wrote: Hi JAC Try below, please. Option Explicit Sub newSheet() Dim ShName As String ShName = "Database" AddSheetSheetNamed ShName End Sub '---------------------------------------------------------- ' Procedure : AddSheetSheetNamed ' Date : 20060312 ' Author : Joergen Bondesen ' Modifyed by : ' Purpose : Add a new sheet and name it. If name exist ' add _Digit format 000 ' Note : '---------------------------------------------------------- ' Function AddSheetSheetNamed(ShName As String) Dim counter As Long Dim ThisWB As Workbook Dim NoofSheets As Long Dim wshExists As Worksheet Dim ShNameOK As String counter = 0 '// This WB Set ThisWB = ThisWorkbook '// New sheet in this workbook, NoofSheets = ThisWB.Sheets.Count If NoofSheets 255 Then MsgBox "You have 255 sheets, sorry.", vbCritical End End If '//Loop ShName = Left(ShName, 28) Do On Error Resume Next Set wshExists = Nothing '// Counter counter = counter + 1 ShNameOK = ShName & "_" & Format(counter, "000") Set wshExists = Sheets(ShNameOK) If wshExists Is Nothing Then _ Sheets.Add().Name = ShNameOK On Error GoTo 0 Loop Until wshExists Is Nothing Set ThisWB = Nothing Set wshExists = Nothing End Function -- Best regards Joergen Bondesen "Jac" wrote in message ... hi, I have created a MS Excel macro that can help me to insert a new sheet and named it as Database but this macro will delete the Database sheet if I run it again. So, I would like to create a MS Excel macro which could help me to name each sheet in series. For example, if previously there is a sheet already named as Database1, then the next time, I run the macro it should name the newly inserted sheet as Database2. Could anyone help? cause I have no ideal how to compare the names of sheets in a workbook. Thanking in advance -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line charting the same series using data from multiple sheets? | Excel Discussion (Misc queries) | |||
how do I fill series on different sheets | Excel Worksheet Functions | |||
Skip Rows in Fill Series and Filter to Two different sheets | Excel Worksheet Functions | |||
Series with named range on several identical sheets | Charts and Charting in Excel | |||
Charting series in different sheets (summarize) | Charts and Charting in Excel |