need a macro

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Hello

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
 
You can do this manually:

Select all your sheets. Select A4:A10 and array-enter:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+ROW()-10

Copy them and paste special as values. Format the cells as mm-dd-yyyy.

As a macro:

Public Sub TabDates()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
If IsDate(wsSheet.Name) Then
With wsSheet.Range("A4:A10")
.FormulaArray = "=MID(CELL(" & """filename""," & _
"R1C),Find(""]"",Cell(""filename""" & _
",R1C))+1,255)+ROW()-10"
.Value = .Value
.NumberFormat = "mm-dd-yyyy"
End With
End If
Next wsSheet
End Sub
 
thank you very much it worked great.
and I'll remember about not posting in two areas.
steve
-----Original Message-----
You can do this manually:

Select all your sheets. Select A4:A10 and array-enter:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +1,255)+ROW()-10

Copy them and paste special as values. Format the cells as mm-dd-yyyy.

As a macro:

Public Sub TabDates()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
If IsDate(wsSheet.Name) Then
With wsSheet.Range("A4:A10")
.FormulaArray = "=MID(CELL(" & """filename""," & _
"R1C),Find(""]"",Cell(""filename""" & _
",R1C))+1,255)+ROW()-10"
.Value = .Value
.NumberFormat = "mm-dd-yyyy"
End With
End If
Next wsSheet
End Sub

steve said:
Hello

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
Click to expand...
.
Click to expand...
 
Back
Top