Naming Consectutive Excel Tabs

  • Thread starter Thread starter mastermk
  • Start date Start date
M

mastermk

Is there a way I can put the DATE on the 3/4th Tab, and have each Tab after
it Auto put the next Date on them, having enough tabs for a month + a few
main page tabs.

Thanks
Mastermk
 
You need a macro to do this..Try the below which will name the 3rd sheet to
the current date ...and on every workbook open check whether the current date
sheet is available. If not a new sheet by the name of current sheet....
--Try this in a blank workbook with 4 sheets
--Try renaming the 3rd sheet to a date 10-15-2009 and open/save/close the
workbook continuosly..The macro should create additional sheets everytime you
open upto the current date.

Set the security level to low/medium in (Tools|Macro|Security). From
workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left
treeview search for the workbook name and click on + to expand it. Within
that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_Open()

Dim ws As Worksheet, strDate As String
For Each ws In Worksheets
If IsDate(ws.Name) Then strDate = ws.Name
Next
If strDate = "" Then
strDate = Format(Date, "mm-dd-yyyy")
Set ws = Worksheets(3): ws.Name = strDate
Else
If CDate(strDate) + 1 <= Date Then
Set ws = Worksheets.Add(After:=Worksheets(CStr(strDate)))
strDate = Format(CDate(strDate) + 1, "mm-dd-yyyy")
ws.Name = strDate
End If
End If
End Sub

If this post helps click Yes
 
see response to your posting in another group.

--
Regards
Roger Govier

mastermk said:
Is there a way I can put the DATE on the 3/4th Tab, and have each Tab
after
it Auto put the next Date on them, having enough tabs for a month + a few
main page tabs.

Thanks
Mastermk


__________ Information from ESET Smart Security, version of virus
signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top