Macro to complete date form

[Closed]
Report
Posts
4
Registration date
Tuesday February 24, 2015
Status
Member
Last seen
February 25, 2015
-
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
-
Hello,

I've got a spreadsheet with the Month in the top left "A5:B7" merged cells, the numerical days of the week (only Mon-Fri) on the right "L9:AJ10".

At the moment I am manually changing the Month on each sheet (30+ in the workbook) and also changing the numerical days for Mon-Fri

I'm just wondering if there was a possibility to create a macro to pop up a message on a master file asking which Month/Year to create the spreadsheet for, this will then create a new spreadsheet using the template automatically changing the Month and date cells.

1 reply

Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Don't really need a macro. I often put a "Driver" worksheet in workbooks I build. One of the drivers (or inputs) could be the start date for the range of dates on the other sheets.

Within the functions, consider using =now() in combo at the right times.
For example, I build 'em so the now() function updates files to the current year when opened.

To find the end of the month, for example, it might go like this:
=EOMONTH(NOW(),0)

To get the day of the month you could use:
=DAY(NOW())

To get the Weekday is a bit more involved.
=CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Hope that helps!
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
4
Registration date
Tuesday February 24, 2015
Status
Member
Last seen
February 25, 2015

Thank you cmarzahn, yes your answer does help. Would it be ok for me to post the master file on here and for you to have a look at this?
Posts
4
Registration date
Tuesday February 24, 2015
Status
Member
Last seen
February 25, 2015

Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
I think I've done what you were thinking.
Look for the "drivers" sheet at the end of the worksheets.
Put the Start of the month in the yellow cell
It finds the day of the end of the month.
Fill in the dates of the first week of the month (Yellow) and the rest happens automatically. Saved a copy to the same place on Google Drive.

https://docs.google.com/spreadsheets/d/1i6KFwZHJLTD0_6uXvsygV1cC9vK5oh8o-rkZyclCOD4/edit?usp=sharing
Posts
4
Registration date
Tuesday February 24, 2015
Status
Member
Last seen
February 25, 2015

Hi,
Yeah, everything is working well here, just need to add conditional formatting. If the days of the week = "0" or " " then grey the cells below out.
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
What a great idea. I was thinking you might be able to massage the formulae in the first week (yellow) to be automatic. Glad it helped.