How to have worksheets autonamed (using a date) when adding

[ExcelNovice]

I am trying to create a workbook that will do this: If I start with just one worksheet, and rename it using a date (i.e. 1-12-12), when I add a new worksheet, I'd like for it to automatically be named 1-19-12. Thus, every time I add a new worksheet, that new worksheet will increment by one week (7 days) from the previous worksheet.

Not sure how to do this, or if it is doable?

Thanks!

[Sisyphus]

Hi,
There are three tasks to what you want to do:
1. Find the previous worksheet
2. Create the name of the next worksheet
3. Name the next worksheet

1. Find the previous worksheet
Worksheets are indexed from left to right as you open the workbook. So, if you always have the last worksheet on the left or on the right you may address it as

Code: Select all

Sheets(1) or
Sheets(sheets.Count)


If this can't work for some reason you need to loop through all the sheets and find the one with the latest date:

Code: Select all

Dim Ws as Worksheet
Dim Dt as Date
For Each Ws in Worksheet
    If Cdate(Ws.Name) > Dt Then Dt = Cdate(Ws.Name)
Next Ws


2. Create the next worksheet's name:

Code: Select all

Dim NextName as String
NextName = Format(Dt + 7, "mm-dd-yy")


3. Name the new sheet

Code: Select all

Dim Ws as Worksheet
SetWs = Worksheets.Add (Before:=Sheets(1))
Ws.Name = NextName


As you can see, I have added code to create the new sheet and place it before the first sheet which you can tweak the way you like, especially, since you are more likely to take a formatted copy of an existing sheet rather than a new one. But that may be another thread, if you need it.

[ExcelNovice]

Sisyphus, thanks for your input! I'm doing something wrong, as I keep getting a compile error. I'm assuming that I put this code into 'This Workbook'?
Here is what I'm putting in:

Sheets(1) or
Dim NextName as String
NextName = Format(Dt + 7, "mm-dd-yy")
Dim Ws as Worksheet
SetWs = Worksheets.Add (Before:=Sheets(1))
Ws.Name = NextName


The compile error is in line 1 at the (1). It is saying invalid outside procedure? I'm lost.

[Sisyphus]

Hi,
I can see where you got the idea for your name.
On a code sheet most code is grouped in procedures - either subs or functions. Very little code is permitted "outside" such procedures. This is the compile error you are getting.

A procedure must be declared and ended. Like this:
Sub YourSubsName()
End Sub
You can choose a name of your liking, more or less. Avoid unusual characters and don't use spaces. The brackets at the end are necessary. The End Sub will be inserted by VB automatically.
All your code must be between the declaration line and the end.
All your declarations should be made at the top of your code. Having a Dim statement somewhere in the middle might actually work, but not always and, anyway, it isn't good practice. So, this is what you get:

Code: Select all

Public Sub AutoNameWs()
    Dim NextName as String
    Dim Ws as Worksheet
    Dim Dt as Date
    For Each Ws in Worksheets
        If Cdate(Ws.Name) > Dt Then Dt = Cdate(Ws.Name)
    Next Ws
    NextName = Format(Dt + 7, "mm-dd-yy")
    Set Ws = Worksheets.Add (Before:=Sheets(1))
    Ws.Name = NextName
End Sub


Note that I added the word Public before the Sub declaration. Procedures are Public by default. So it isn't necessary, but code is easier to read if you don't have to read what is missing in addition to what is there. I added the Dt variable for you because if you don't first create it you can't very well use it to create the NextName. Finally, note how "brackets" are created by indenting blocks of code. That also serves the purpose of making the code easier to read.

I have inserted the code in the ThisWorkbook code sheet in the attached workbook. The dates produced by the code aren't what you would expect. That is because my system works with dd.mm.yy dates. Therefore it can't understand the dates formatted as mm-dd-yy. Once you have a workbook where all dates were produced and written by the same system the problem will go away. It will come back though, if you allow a difference between the day-month sequence in your Regional Settings and the format of the date in the tabs - as the attached workbook demonstrates.

[ExcelNovice]

Ah...PERFECT! Thanks so much! The only changes I made to it was to do it AFTER in lieu of BEFORE and then Sheets.count

Works like a champ! Thank you so much!