date and day question

[ajm]

I had something like this very recently but can't find it anywhere. I am adding a command button to create a new workbook. this new workbook needs a date extension on the end of the standard name. I want this date extension to be the Thursday of the current week. That is, press the button on tuesday 17 June or Wednesday 18 June and the file created will be named "File 19062008.xls".

cheers

ajm

 

[Simon Lloyd]

I have provided code for weekdays only but you get the idea for extending it!

Sub datez()
Dim MyDate As Date
Select Case Format(Date, "ddd")
Case Is = "Mon"
MyDate = Date + 3
Case Is = "Tue"
MyDate = Date + 2
Case Is = "Wed"
MyDate = Date + 1
Case Is = "Thu"
MyDate = Date
Case Is = "Fri"
MyDate = Date - 1
End Select
ThisWorkbook.SaveAs (ThisWorkbook.Name & " " & Format(MyDate, "ddmmyyyy"))
End Sub

 

[ajm]

Thanks Simon. Not waht I originally had but does the trick perfectly. Many thanks.