Name Sheets in Series

[Guest]

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]

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/buildtoc.htm#sortallsheets

 

[Dave Peterson]

Add an "on error goto 0" after the Loop statement:

Loop
On error goto 0

msgbox wks.name

end sub

 

[Joergen Bondesen]

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

 

[Don Guillett]

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]

typo due to testing

If UCase(Left(ws.Name, 2)) = "DATABASE" Then

If UCase(Left(ws.Name, 8)) = "DATABASE" Then


--
Don Guillett
SalesAid Software
(e-mail address removed)

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
(e-mail address removed)

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]

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]

Hi Dave

Thanks.
I will ajuste my sampel.

I do not recall where I have than number from.
Older version of Excel perhaps?

 

[Don Guillett]

Glad to help.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks, Don!

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
(e-mail address removed)

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]

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]

Hi Dave

I will not bet with you, because I will lose.

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.

 

[Joergen Bondesen]

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

typo due to testing

If UCase(Left(ws.Name, 2)) = "DATABASE" Then

If UCase(Left(ws.Name, 8)) = "DATABASE" Then


--
Don Guillett
SalesAid Software
(e-mail address removed)

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
(e-mail address removed)

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

 

[Don Guillett]

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
(e-mail address removed)

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

typo due to testing

If UCase(Left(ws.Name, 2)) = "DATABASE" Then

If UCase(Left(ws.Name, 8)) = "DATABASE" Then


--
Don Guillett
SalesAid Software
(e-mail address removed)

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
(e-mail address removed)
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

 

[Don Guillett]

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
data>filter>autofilter for what you need?

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi Dave

I will not bet with you, because I will lose.

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.

 

[Dave Peterson]

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.

Hi Dave

I will not bet with you, because I will lose.

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.

 

[Joergen Bondesen]

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.

--
Best regards
Joergen Bondesen

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
data>filter>autofilter for what you need?