Request for a macro insert name of the day in adjacent column to date in edairy 2021

Hi
Here I am enclosing a sample of ediary of Jan 2021. I am willing to insert the name of the in the cell “I”. But the cells from E to K is merged. First we have unmerge the rows where the date is placed and again merge the cells from E to H. In the Column “I” the name of the day ie. the formula
=TEXT(6,”DDDD”) till end of the month. After the cells I to K shall be merged. The first date is placed on 6th row, second is 46th row, and third is 86th row and so on. ie. after every 40 rows. I request for a macro to accomplish the above requirement. As I have enclosed only the Jan 2021 sheet, I request yourselves to provide the macro for another 11 sheets.

Thanks in Advance.

Buvanamali

Hi

Any help please.

I am very much thankful to you for all your help provided by you earlier.

Once again thanks. Please help me. If I am not clear in my requirement, kindly advise, I am ready to explain in detail.

In anticipation of your help.

Buvanamali

Try this code

Please Login or Register  to view this content.

Hi

Thank you so much. It worked like a charm.

One more help - As these diary is having sheets for other purposes, shall we restrict the execution of the above macro to monthly sheets viz. Jan to Dec.
Further All Sundays, 2nd and 4th Saturdays filled with different colour to highlight as Holiday.

Once again thanks lot with regards.

Stay Safe. Work Safe.

Buvanamali

Originally Posted by maniacb

Try this code

Please Login or Register  to view this content.

Shouldn't all of your cell objects reference ws ?

in order to restrict this code to the monthly sheets, what are the names of the other sheets. Better yet, can all those sheets have an underscore value, that way I can restrict this code to any sheet without an underscore.

Rick, good catch

@maniacb: With you newest code, shouldn't all of your cell objects have a dot in front of the so that they reference the ws object of the With statement?

Hi

I am happy that Mr Rick also helping me. Really I am more proud to pay more immediate attention to facilitators. Today I was away from my home that is why I could not attend to your reply immediately. I regret the inconvenience.
My monthly sheets are named as “Jan, Feb, Mar ……. Dec” So there is no _ (underscore as you mentioned)
With the post no.3, I was able to insert day as per my requirement. I executed it sheet by sheet pressing F8.
As per Rick’s advice, I tried by F5, it didn’t make changes in all the as per my original request.

Then I tried Mr Maniacb code as shown in post no.8, but there was no result except the error in line “If InStr(ws.Name,”_”)>=1 Then GoTo Skip”. I am uploading the Original File. My aim is to accomplish my desire.
OK my requirement is as below:

1.My requirement as per your post no.3 is working fine. Here my aim is, it should not affect the other sheets except monthly sheets as shown in the example.
2. Give Read as a colour for Sunday as Weekly Holiday.
3. Give Light maroon colour for Second Saturday and Fourth and Fifth Saturday as Weekly Holiday.
Nothing more than the above is expected Sir,
Advance thanks for all who help me.
Stay safe. Work safe.
With kind regards

Buvanamali

Hi

It's amazing. I respect your knowledge. Finally one request, as of now I am changing year in (E6) only. It changes the New year for Jan and Feb. Again I am changing the year in March Sheet (E6). This will change the rest of the months upto December. Can you help me to enter the year in the input box so that the dairy shall be created for any year.

Advance thanks.

Stay Safe. Work Safe.

With regards

Buvanamali

This code will ask you for the year and will update each monthly tab with the first of that month using the tab name. Let us know if you have any questions.

Please Login or Register  to view this content.

Dear Mr Maniacb

Your code working amazingly. Thank you very much dear. One more request, the DATE in column B is right aligned, that shall be center aligned. That's all.


Advance Thanks.

Stay Safe. Work Safe.

With regards

Buvanamali

Here you go

Please Login or Register  to view this content.

Dear Mr Maniacb

Thank you for your kind and quick help. I make the thread as solved.

Thanks once again.

Stay Safe. Work Safe.

With regards.

Buvanamali