Is there a way to name tabs so that they will ascend in order?

M

madchef

I am trying to label excel tabs so that they will go up automatically in
order (ie. Jan 1, Jan. 2, Jan. 3, etc).
 
B

Bob Phillips

myDate = DateSerial(2006,1,1)
For i = 1 To Activeworkbook.worksheets.count
Worksheets(i).Name = Format(myDate,"mmm d")
myDate = myDate + 1
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Dave Peterson

Just a suggestion before you get too far into your workbook design.

You may want to use this variation of Bob's routine:

myDate = DateSerial(2006,1,1)
For i = 1 To Activeworkbook.worksheets.count
Worksheets(i).Name = Format(myDate,"yyyy_mm_dd")
myDate = myDate + 1
Next i

It'll make sorting the sheets much easier if you have to do this later.
 
R

romelsb

I like this topic especially when dates are pre-determined....I like to do
this workbook design also yet considering only 6 days a week....hope we may
have this good for office dummie like me..........
 
D

Dave Peterson

Change the starting date, ending date and format--and the day to skip:

Option Explicit
Sub testme()

Dim iDate As Long

For iDate = DateSerial(2006, 1, 1) To DateSerial(2006, 5, 12)
If Weekday(iDate) = vbSunday Then
'do nothing
Else
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(iDate, "yyyy_mm_dd dddd")
End If
Next iDate
End Sub
 
R

romelsb

Hello Dave...never been in VBE...hope u dont mind to teach me how and where
to paste everybody's good idea...thanks
 
D

Dave Peterson

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there. (But change the name from TestMe to something
significant.)

Now go back to excel to test it out.

Tools|Macro|macros|select that macro and click Run

Save your workbook before you run the macro--then if you have trouble, you can
close without saving and bring things back the way they were.

Note: DateSerial(2006, 1, 1) is dateserial(year#, month#, day#)
 
Top