Hey there,
I'm developing a macro that creates an xlsx file and saves the file name as tomorrow's date. I need to save as tomorrow's date because the file this procedure is creating will be imported into an accounting program the following day as long as our cross-checkers find no errors. The file I create today will be imported tomorrow, but what if tomorrow is a Saturday? I would actually then need to save the file as today + 3 so it has Monday's date. Is there anyway to account for this in VBA? My biggest issue here is I have 4 options the user will need to choose from. The options are New Award Letter, Correction (same day), Correction (prior day), and Revision, and I need to explain these options so you can fully understand my problem.
The New Award Letter option will check if the file already exists, if not it will create the file and copy a range to it. If it does exist, the macro will copy the range to the file, search for duplicate SSN in column B, and delete the row if a duplicate is found.
The Correction (same day) option will be used if the user noticed they made a mistake and need to make a correction on the same day. It basically does what the New Award Letter option does, except it won't create a new file because if the file it's looking for doesn't exist, that means someone deleted the file (which is a no-no) or something has glitched and I will need to get involved.
The Correction (prior day) option is the same as the Correction (same day) option, expect the user will use this only if they need to make a correction the day after they created the initial file. This is where my problem comes in. The same day correction option looks for a file with tomorrow's date. The prior day correction option looks for a file with today's date.
If a user needs to make a prior day correction on a Monday, right now the file the procedure is searching is saved as Saturday's date, which means it isn't going to find a file because this procedure looks for for a file name that is saved as today's date.
I think I may be able to work with some formulas in a sheet, but if possible, I'd rather solve this through VBA. Part of my code is below, I'd post the whole thing but it's too long. Is there anyway in VBA to tell if today is a Friday? That could solve my problem pretty easily if so
Bookmarks