Need sopme help coding Dates and Sheet names. (Please)

[Dean Goodmen]

I could use some help codeing please...


There have 5 worksheets, each a week of the month. From the Year and
month inputed, it should name each sheet by the last day in each week.
(Saturday) All the lines with a ' starting it was code I was not sure
how to write. (some code ommited to clean things up)

The following data is selected on the first Sheet. D2 = Month(TEXT)
H2= Year(Number)


If Not IsEmpty(D2) And Not IsEmpty(H2) Then
'SET SHEETDATE Month(D2) Year (H2) DAy(First Saturday of the Month)
For Each Sh In ThisWorkbook.Worksheets
If Sh.Index <> 6 Then
Worksheets(Sh.Index).Activate
SheetDate = DateAdd("ww", (Sh.Index - 1), SheetDate)
'If this date is Not Past the end of the month in D2 Then
Sh.Name = Format(SheetDate, "mmm-dd")
'Else
Sh.Name = "Week " & Choose(Sh.Index, " 1", " 2", " 3", "
4", " 5")
'End if
End If
Next
End If

For example if D2 = Feburary and H2 = 2005 Then the sheets would name
them selfs like so :

Sheet 1 Renamed to Feb-05
Sheet 2 Renamed to Feb-12
Sheet 3 Renamed to Feb-19
Sheet 4 Renamed to Feb-26
Sheet 5 Renamed to Week 5

[Leith Ross]

Hello Dean,

Here is a macro you can use. Add a module to your VBA project and paste this code in. You can select and run it from Excels menu "Tools | Macro | Macros... "
The macro name is "RenameSheets".

_________________________________________________________________

Public Function FindSaturdays(strMonth As String, intYear As Integer) As Variant

Dim N As Integer
Dim DaysInMonth As Integer
Dim StartDate As Date
Dim Saturdays(5)

StartDate = "01-" & strMonth & "-" & LTrim(Str(intYear))

Select Case Month(StartDate)
Case 2
DaysInMonth = 28
Case 4, 6, 9, 11
DaysInMonth = 30
Case Else
DaysInMonth = 31
End Select

For I = 1 To DaysInMonth
If WeekDay(StartDate) = 7 Then
N = N + 1
Saturdays(N) = Format(StartDate, "mmm-dd")
End If
StartDate = StartDate + 1
Next I

FindSaturdays = Saturdays

End Function

Public Sub RenameSheets()

Dim Saturdays

Saturdays = FindSaturdays(Range("D2").Value, Range("H2").Value)

For I = 1 To UBound(Saturdays)
Sheets(I).Name = Saturdays(I)
Next I

End Sub_________________________________________________________________

Hope this helps,
Leith Ross