Clear Calendars of List From Holidays Sheet

[xjohnson]

Hello,

Is there a way to clear the calendars of the list from the Holidays! sheet when changing the year in the Setup sheet?

Thank you,
XJ

[jeffreybrown]

What do you mean by clear when changing the year?

[xjohnson]

I plan to use this in the 2019 year so when I change the date in the Setup sheet for 2019 I want the holiday list from 2018 that were put in the holiday sheets to clear.

For instance in January sheet the range A4:N8 to clear contents and repeat to the next rows down and then for each other calendar sheet.

So if I wasn't clear.

I appreciate the help

[jeffreybrown]

Ok, but let me ask first, why clear 2018 when you can just create a huge list of holidays from 2018 thru as many years as you would like.

[xjohnson]

The Holiday sheet has US Holidays plus the other days that my company decides to take off so it would be nice to be dynamic for each year please.

I suppose I can use this:

Worksheets("January").Range("A4:N8").ClearContents

Then put one line for each range on each sheet. I think it's about 60 lines of code.

[jeffreybrown]

Try this...

Please Login or Register  to view this content.

How can the holiday's be dynamic when you say, "plus the other days that my company decides to take off".

Again, why not just build 2019 below 2018, 2020 below 2019 etc. Other then that, still not clear on what you want besides the macro above to clear the month tabs.

As you can see, I didn't fill in all the month tabs, but I'm sure you can fill in the rest.

[xjohnson]

Jeffrey each year the holidays aren't on the same day of the week so my company will decide which days to take off based on the days they fall. Yes I agree I can make one for each year manually but I thought it would be more useful this way sorry if it's too much trouble.

[xjohnson]

I added the other months but it didn't clear the contents:

Please Login or Register  to view this content.

[jeffreybrown]

It's not too much trouble, just trying to understand how you make a formula to work on the company deciding what day to take off.

As far a manually, you don't have to make the holiday manually. You can use a formula such as you have, but also take a look here.

In this example, the formula in column C is tied to the named range theyear, but you could add in column D the year and then replace that in the formula.

Can you give me an example?

I added the other months but it didn't clear the contents:

I updated the code in post #6. Sorry my fault as I missed a couple of important items.

[xjohnson]

Thank you Jeffrey you were a tremendous help today.

The only thing lacking now is to fill in December with dates that show the first few days in January in case we take them off.

If it's the 2018 year it should show 'New Year's Day' in the Jan 1st spot. Then also show if we take a few days off after that.

Not sure if this is possible.

Thanks,
XJ

[xjohnson]

I think maybe a separate list for these extra days?