View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Dean Goodmen Dean Goodmen is offline
external usenet poster
 
Posts: 18
Default Need sopme help coding Dates and Sheet names. (Please)

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