Macro Date Selection

[ponrajnadar28]

I wanted to make it to where anytime I first go to a Report worksheet (like “Sheet1”), it defaults to the 4 most recent weeks’ worth of date:

• “To” defaults to date with most recent data.
• “From” defaults to 4 weeks prior to most recent data (“To” date minus 21 days).

Afterwards, anyone can change the date ranges to any date they want from the drop-down (they might only want to see 1 week, or maybe 8 weeks, etc.). Only when the worksheet initially activates do I want it to default to most recent 4 weeks’ worth of data.


I think the best way to accomplish this would be to use the Worksheet_Activate in Module1. Before it does any window resizing (maybe immediately after Application.ScreenUpdating = False ) to change the “To” date in Cell J8 to the most recent date option before today’s date ( DATE() ), and then change “From” in Cell H8 to equal the Date in J8 – 21 days .

Then rest of “Worksheet_Activate()” can continue with resizing the windows. And after done, the user can manually change the From/To dates to anything they want