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.