Getting activesheet.range to refer to a name that varies...

you need patience - this is a worldwide forum so members are online at different times. Also, the help is offered for free & depends on members having spare time.

Your idea will prove cumbersome, needing to create all those named ranges. I have amended your calendar so that the days are generated by formulas and formatted as dd to display the number.

The comboboxes load correctly when the sheet is activated.

Enter the date by clicking into C7 to display a calendar to pick the date

The data is input into the day on the calendar sheet