Calculating Friday's

[holland3777]

I was wondering if there is a way to calculate every friday in a year using VB and place those dates on the sheet tab as names?

 

[longhair]

holland3777,
take a look at the weekday function and i believe the worksheet function.
regards,
longhair

 

[AnotherHiggins]

Have a look at faq68-5827, Why do Dates and Times seem to be so much trouble?

Once you understand that dates in Excel are just stored as whole numbers (and times as decimals - percentages of 24 hours), you can see that all you have to do to 'calculate every friday' is start with ONE Friday and add seven to it. Keep adding 7's and you keep getting Fridays.

It seems like you just want an easy way to create 52 sheets - one per week. If that is the case, then try the following (untested):

Sub AddSheets()
Application.ScreenUpdating = False
FirstFriOfYear = "[red]01/07/05[/red]" [green]'Change date to the first Fri of any year[/green]
myYear = Year(FirstFriOfYear)
ActiveSheet.Name = Format(FirstFriOfYear, "mm-dd-yy")
Do
    LatestDate = DateValue(ActiveSheet.Name)
    Sheets.Add Type:="Worksheet"
    ActiveSheet.Name = Format(LatestDate + 7, "mm-dd-yy")
    NmbrShts = ActiveWorkbook.Sheets.Count
    ActiveSheet.Move After:=Sheets(NmbrShts)
Loop Until DateValue(ActiveSheet.Name) >= DateValue("12-31-" & myYear)
Application.ScreenUpdating = True
End Sub

PLEASE NOTE - This is a terrible way to STORE your data. If you only want to create these sheets for reporting reasons that's fine, but it is far, far, far better to keep all of the data stored in one table, then pull weekly (or monthly, quarterly, Y-T-D, Annual) data out later.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.