Hello
I put htis in worksheet functions also but I think it
should have been here. sorry

I hope some one can help me with this.
I have a work book with 52 sheets in it. each sheet
represents a week of the year with the tab being labeled
as the last day of the week ( 1-1-2005 )ect. which is
saturday. cell a10 on all the sheets is saturday I would
like a macro that would put the dates on all 52 sheets so
that a10 matches each sheet tab going backwards to a4
which would be the sunday before. ( desending )
this is the macro that I am trying to make work but it has
an error I can't figure out.
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If CDate(wks.Name) 0 Then
wks.Range("A10").Value = CDate(wks.Name)
For i = 6 To 1 Step 1
wks.Range("A10").Offset(i, 0).Value = CDate
(wks.Name) + i
Next
End If
Next wks
End Sub

Thanks in advance
Steve

..

Where's the error?

Not entirely certain I've got your logic correct either, but here goes...

Sub test()
Dim wks As Worksheet, i As Long

For Each wks In Worksheets
If IsDate(wks.Name) Then
For i = 0 To 6
wks.Range("A10").Offset(-i, 0).Value = CDate(wks.Name) + i
Next
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"steve" wrote in message
...
Hello
I put htis in worksheet functions also but I think it
should have been here. sorry

I hope some one can help me with this.
I have a work book with 52 sheets in it. each sheet
represents a week of the year with the tab being labeled
as the last day of the week ( 1-1-2005 )ect. which is
saturday. cell a10 on all the sheets is saturday I would
like a macro that would put the dates on all 52 sheets so
that a10 matches each sheet tab going backwards to a4
which would be the sunday before. ( desending )
this is the macro that I am trying to make work but it has
an error I can't figure out.
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If CDate(wks.Name) 0 Then
wks.Range("A10").Value = CDate(wks.Name)
For i = 6 To 1 Step 1
wks.Range("A10").Offset(i, 0).Value = CDate
(wks.Name) + i
Next
End If
Next wks
End Sub

Thanks in advance
Steve

.

See an answer in worksheet.functions.

In general, most of the regulars read both worksheet.functions and
..programming (along with .misc), so if you've posted in one place, it's
usually better to not repost in a second group - it just fragments your
answers and potentially wastes time of those answering a question that
has already been answered.

In article ,
"steve" wrote:

I put htis in worksheet functions also but I think it
should have been here. sorry

Thanks again, hope you all have a good life
steve
-----Original Message-----
Hello
I put htis in worksheet functions also but I think it
should have been here. sorry

I hope some one can help me with this.
I have a work book with 52 sheets in it. each sheet
represents a week of the year with the tab being labeled
as the last day of the week ( 1-1-2005 )ect. which is
saturday. cell a10 on all the sheets is saturday I would
like a macro that would put the dates on all 52 sheets
so
that a10 matches each sheet tab going backwards to a4
which would be the sunday before. ( desending )
this is the macro that I am trying to make work but it
has
an error I can't figure out.
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If CDate(wks.Name) 0 Then
wks.Range("A10").Value = CDate(wks.Name)
For i = 6 To 1 Step 1
wks.Range("A10").Offset(i, 0).Value =
CDate
(wks.Name) + i
Next
End If
Next wks
End Sub

Thanks in advance
Steve

..


.