VBA - Saving a file with a filename relative to the current date (sort of)....

Posted by Chris Rock on November 13, 2001 9:09 PM

I've got a report I'm trying to automate. Part of the report requires a macro...and part of the macro I want to create takes a worksheet and saves copies it to a new workbook, and gives it a filename.

Here's the tricky part - I'd like the filename to be related to the date - the date of the prior MONDAY, if possible.

How would you do that? Could you figure it out in a formula on the worksheet and reference that cell as the filename?

I am putting this macro into a template file, so it would only be run once. It would need to figure the current date and work its way back to the prior Monday. I suppose some kind of loop where it compares the current day with the prior Monday (and that means if it's CURRENTLY a Monday it would look back 7 days, but if it's CURRENTLY a Tuesday it would look back 8 days, not 1 day).

I know this is complicated, but can anyone help me? Anyone done this before?

Thanks in advance for any help.

Posted by Barrie Davidson on November 13, 2001 9:23 PM

Chris, you could do this in VBA (i.e., you don't need to reference a cell in the worksheet). Something like this:

If WeekDay(Now, 2) = 1 Then
YourVariableName = Format(Now - 7, "mm-dd-yy")
Else
YourVariableName = Format(Now - (WeekDay(Now, 2) + 7), "mm-dd-yy")
End If

This will set the variable "YourVariableName" to the required date in a format like 11-04-01.

Does this help you out?
BarrieBarrie Davidson