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

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

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

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

Name Sheets in Series
 

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

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

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

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

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

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

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

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

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

Name Sheets in Series
 

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

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

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