need a macro

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

hello
Lets see if I can explain what I need.
I have a workbook with 52 sheets dated 7 days apart, with
the tab date being the last day of the week. (saturday)
on each sheet I have 7 cells for the days of that week.
right now I go to each sheet and type the first date of
the week then drag down to the 7th day 52 times.
is there a macro that will do this for me.
thanks
steve
 
Hi

This open event does what you need, but only until it stumbles over tab with
name unvalid as date. I haven't found an acceptable way to test, is the
string a valid datestring jet, and I can't spend too much time on it. Maybe
someone other can help you further along.


Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If CDate(wks.Name) > 0 Then
wks.Range("A1").Value = CDate(wks.Name)
For i = 1 To 6 Step 1
wks.Range("A1").Offset(i, 0).Value = CDate(wks.Name) + i
Next
End If
Next wks
End Sub
 
Hi again

A new version - the one with error testing (works when you have non-date
tabs too):

Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
TabDate = 0
For Each wks In ThisWorkbook.Worksheets
TabDate = CDate(wks.Name)
ItIsDate = False
If TabDate > 0 Then
wks.Range("A1").Value = TabDate
For i = 1 To 6 Step 1
wks.Range("A1").Offset(i, 0).Value = TabDate + i
Next
End If
Next wks
End Sub
 
Hi

Another way - use UDF

Create a function in some module

Public Function CurrentTab()
Application.Volatile
CurrentTab = Range("A1").Worksheet.Name
End Function

On worksheet, enter into cell p.e. A1 the formula
=IF(ISERROR(DATEVALUE(CURRENTTAB())),"",DATEVALUE(CURRENTTAB())
Into A2 enter formula
=IF($A$1="";"";A1+1)
copy the formula into range A2:A7
Format the range A1:A7 as date
 
It sounds as though this could be done using regular formulas if you have a
recognizable date in the sheet name.
Can you give an example how the first and the last sheet are named?
 
Hi

You mean
=MID(CELL("filename",A1),FIND("]",CELL(("filename",A1)),99)
to get the sheet name? Then the final formula will be:
=IF(ISERROR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL(("filename",A1))
,99))),"ErrorResponse",DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL(("fil
ename",A1)),99)))

Maybe a little UDF is preferable anyway?
 
well the consists of 1 sheet for each week of the year.
then on each sheet i have the days of the week sunday thru
saturday and the dates in the column in front of the days,
the tabs on the 52 sheets are labled with the date satudy
falls on because that is the end of our week and i print
the sheet and turn it in. then move to the next one and at
the end of the year i have a sheet that keeps totals for
the year. right now i go to each sheet type in sundays date
and drag down to saturday. now i want to make up the books
ahead a few years. so i would have to be able to put in
the dates i want so it wouldn't use the computer date.
hope this makes sence.
thanks
steve
 
Back
Top