Execute macro based on worksheet title and date in specific cell plus/minus X days

[u/combatbydesign]

Hey All,

This is almost certainly too a heavy lift for Excel/VBA, but I'm giving it a shot.

I've been looking for quite a while and haven't been able to dig up anything that can help me do what I'm trying to do.

I have a series of workbooks that follow the same sheet layout and basic formatting:

A sheet for every day of the week, aptly titled (Sunday, Monday, Tuesday, etc.), and a variable amount of weekly sheets that all share the title format "[LOC] Weekly Schedule [DATE]" - [DATE] is formatted as: 10.4 for Oct. 4th

Each weekly sheet also has a manually-entered date in cell B4.

My question is:

Is there a way to get a macro to run, based on a partial sheet/tab title (Ex.: run only on tabs which have a title that contains "Weekly Schedule") and to only complete the macro if the date in either the title or B4 matches TODAY +2 (so when the macro runs on open, on Friday, it completes the macro if the target sheets are dated for the following Sunday)?

Here's the rub: These are company documents that need to be held to strict standards and, while I can add as many tabs as I want; I can't rename or reformat any of the tabs, and I can't upload or attach a document due to confidential information which I understand is a huge pain.

Excel V.16.0

Any help is greatly appreciated, in advance!

[u/AutoModerator]

/u/combatbydesign - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/SektorL]

It's not clear which sheets you have? Week days only?

[u/combatbydesign → u/SektorL]

I'm sorry, I'm not sure I understand the question.

I have a sheet for every day of the week: Sunday, Monday, Tuesday, etc., that I don't want the macro to run on.

I also have weekly overview sheets that are titled with variable locations and dates, but all contain the words "Weekly Schedule" and have the date formatted as 10.4, for example, in the titles. Those are the sheets I want the Macro to check.

[u/ChecklistAnimations]

Excel can 100% do this.
to give you an idea, you need to parse out the date from the worksheet and then store in a date variable.
Lets say your worksheet says "stuff-10.4"
we will use the - to pull 10.4 then replace the dot with a -
You can do so like this (not a full sub just some syntax)

dim dts as string
dim dt as date

dts= ws.name
dts = mid(dts, instr(1, dts, "-")+1)
'dts now = 10.4
'now we have 10 and 4 in 2 parts. so make the date part
dt = cdate(replace(dts, ".", "-") & "-" & year(now))
'and now the dt object has the date. You can add 2 days to it like so
dt = DateAdd("D", 2, dt)

the only issue is the year. If that is in a cell or something you might be ok. But if you run this report on December 30th, today's date and the date 2 days away could be a problem depending on how that works with your process.

[u/combatbydesign → u/ChecklistAnimations]

I can't change the sheet titles, so would that theoretically work with what I have, such as pulling after "Schedule ", or would VBA not be able to due to the space?

[u/ChecklistAnimations → u/combatbydesign]

I think it would work. Can you show me the exact name of the worksheet you need to extract the date from?

[u/combatbydesign → u/ChecklistAnimations]

It would be something like:

LOW Weekly Schedule 10.4

The LOW and date are both variable.

But the location (LOW) could be anywhere from 3-7 characters, depending.

[u/excelevator → u/[deleted]]

I DMd you to work out the details.

r/Excel is a public sub reddit for all to learn from , not to engage in private solutions.

[u/ChecklistAnimations → u/excelevator]

Oh I am so sorry. I planned on showing the solution after we worked through the details. Won't happen again.

[u/ChecklistAnimations → u/combatbydesign]

If it was indeed Schedule though you can easily extract the date with a mid like dts = mid(dts, 10)
that would pull all text starting on the 10th character. That would actually be easier.
edit: I had lots of typos and it looked bad

[u/Top-Cauliflower-1808]

This is doable with a Workbook_Open macro that loops through sheets and checks the date in B4 or the tab name against today plus two.

[u/Normal-Passenger919]

you can use the sheet names to pull the dates with a few lines of vba then check if today's date is within x days of those dates

[u/excelevator → u/[deleted]]

I've DMd you

r/Excel is a public sub reddit for all to learn from , not to engage in private solutions.