Date all tabs

[smithomatic]

Hey guys,

Anyone know a way to automatically date all the tabs in my workbook?

I.e. If I have 365 dates or less that I wish to assign to all the tabs on a sheet can I do this?

Perhaps run a macro than asks you to enter a named range then creates that many tabs and labels them with the values in the named range (assuming your workbook is currently on one tab)

any help apprecriated thanks

 

[Trevor G]

You can't have more than 255 worksheets in a single workbook.

This code is a simple way to add sheets via an inputbox see if it helps you get going

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddTabs1()<br><SPAN style="color:#007F00">'This code will ask you how many sheets you want to add</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> NumSheets <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>NumSheets = InputBox("Enter the number of sheets you need", "Test")<br><SPAN style="color:#00007F">If</SPAN> NumSheets > 0 <SPAN style="color:#00007F">Then</SPAN> Sheets.Add Count:=NumSheets<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[FishingIsMoreFun]

I am sorry this is not a solution, though I would also be interested to know how this is done as I have done it in the past.
There is an issue with the Worksheet Tab names not accepting certain formats.

A start anyway.

Sub NameWorkSheets()

    
    For Each i In Range("named_range")
     Sheets.Add
     ActiveSheet.Name = i
    Next i
    
    
End Sub

EDIT- oops did not see the above post. (Mine will work with text names, and formats the Tab will accept).
Does the above solution address the Date Format issues?
For example many dates appear in this format - "21/02/2011""

Simply changing the format on the worksheet does not seem to work as Excel appears to store some dates in the original format.

 

[FishingIsMoreFun]

Here is the same code with user input.

Sub NameWorkSheets()

    UserInput = InputBox("Please Enter Your named Range", "Name Tabs")
    
    For Each i In Range(UserInput)
     Sheets.Add
     ActiveSheet.Name = i
    Next i
    
    
End Sub

 

[FishingIsMoreFun]

This Works for different date formats.

You have to define a named range and you can edit the format in the code as needed.

Cheers

Sub NameWorkSheets()


Dim mydate As String

    UserInput = InputBox("Please Enter Your named Range", "Name Tabs")
    
    For Each i In Range(UserInput)
    
      newdate = i
      mydate = Format(newdate, "dd-mm-yyyy")
      Sheets.Add
      ActiveSheet.Name = mydate
      
    Next i
    
    
End Sub