Thread
:
Name Sheets in Series
View Single Post
#
16
Posted to microsoft.public.excel.programming
Joergen Bondesen
external usenet poster
Posts: 110
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 With Quote
Joergen Bondesen
View Public Profile
Find all posts by Joergen Bondesen