Automatic sheet tab changing by date

[rustybenson]

Is there away that i can change sheet tabs automatically.
Lets say I have a wookbook with 31 sheets in it for days of the month.
When I put the date in say the 1/08/2007 (aust dd/mm/yyyy) it will automatically change the other tabs to the folowing day(s) the next sheet tab to 2/08/2007, the next sheet tab to 3/08/2007 and so on......
Thank You
Rusty

 

[Peter_SSs]

Rusty

You will need to use vba to achieve this. Here is something to test to see if this is on the right track. I suggest that you test it on a copy of your file.

If this does look like the right sort of thing, we may need to tweak it for things like months that have less than 31 days etc.

If you need help with how to implement the vba code, post back.

Note also that you cannot have a "/" in a sheet name, so I have used a format like "01 08 07" for the sheet names.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> StartDate <SPAN style="color:#00007F">As</SPAN> Date
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$1" <SPAN style="color:#00007F">Then</SPAN>
        StartDate = Target.Value
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            ws.Name = Format(StartDate + c, "dd mm yy")
            c = c + 1
        <SPAN style="color:#00007F">Next</SPAN> ws
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>