Need help with a macro

[imported_unknown]

I have a spreadsheet for each month of the year, all in the same workbook. The sheet tabs represent each day of the month. I name the tabs, Monday 9-01-02, so on until the end of the month. Cell "B1&C1"are merged and have the "Day" typed in. Cell "D1&E1" are merged and have the date typed in. When I type the date in "D1", it will date the rest of the sheets through the end of the month. I have tried to write a Macro to do this, and it would not work. Can someone help me with this?

Thanks,
DaveB

 

[bolo]

alright this macro should do what you want.
It is set up for October but it can easily changed for any month. Let me know if it works for you.

Public Sub nameSheets()
Dim i As Integer
Dim week(7) As String
Dim month as string

week(1) = "Monday"
week(2) = "Tuesday"
week(3) = "Wednesday"
week(4) = "Thursday"
week(5) = "Friday"
week(6) = "Saturday"
week(7) = "Sunday"
month = "-10-02"
For i = 2 To 31
Worksheets.Add
ActiveSheet.Name = week(1 + i Mod 7) & " " & i & month
Next i
End Sub

 

[Legacy 98055]

Hi Dave.
This is similiar to Bolo's reply except the value in D1 is validated and sheets are renamed as is but added if nec.
Right-click on your worksheet tab and choose view code, paste in.
<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, NextDay As Date
On Error Resume Next
If Target.Address = "$D$1" Then

If Not IsDate(Target.Value) Then
MsgBox "Invalid Date"
Target.Select
Exit Sub
End If

If MsgBox("Rename all of your worksheets for " & _
Format(Target, "MMMM YYYY") & "?", vbYesNo) = vbNo Then Exit Sub

NextDay = Target.Value

Do
i = i + 1
NameAdded:
Sheets(i).Name = Format(NextDay, "DDDD M-D-YY")
If Err.Number = 9 Then
Err.Clear
Sheets.Add , Sheets(i - 1)
GoTo NameAdded
End If
NextDay = NextDay + 1
Loop Until Month(NextDay) <> Month(Target.Value)

End If
End Sub

</pre>
Tom

 

[Dave Patton]

To create sheets for a new month, open a new WB and name it.
Name a Sheet "the names" and add the names that you want to use for the sheets.

To create the names in YOUR format, you could put Year in C1 (2002), month number in C2 (10)
A1 is =DATE(C1,C2+1,0) A2 is =A1-1 Copy down 31
custom format dddd mm-dd-yy
Select the range A1:A31 name it "SheetNames",
Copy the range, then Edit Paste Special as Values

Add the following Sub

Sub NameS()

Dim I As Integer
For I = 1 To 31
Worksheets.Add
ActiveSheet.Name = Sheets("the names").Cells(I, 1).Value
Next
End Sub


The above is untested. Please advise if it works OK.

The above should work with a list of
unique valid names.
This message was edited by Dave Patton on 2002-10-06 22:02