Calculating Excel Tab Names with VBA

[hahnsm]

I have a workbook that contains expense reports. There is a worksheet for every pay period for the year. I have each tab name read the date of every pay date which is every Friday of the year. I created code that reads a worksheet that has all of the dates typed out and the code reads a cell and places that cell's content as the next worksheet's tab name.
I hate typing out the dates at the beginning of the year to calcuate all of my tab names. How can I code so that VBA figures out the date of every Friday and places those dates as my tab names?

 

[PHV]

A starting point:
myYear = 2005
d = DateSerial(myYear, 1, 1)
For i = 0 To 5
If Weekday(d + i) = vbFriday Then Exit For
Next
d = d + i
For i = 0 To 366 Step 7
If Year(d + i) = myYear Then Debug.Print Format(d + i, "Long date")
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hahnsm]

Thanks! I will give it a shot!