Using VBA to determine Day of The Week and copy to Worksheet X if today or Y if another day

[austie]

Hi All,

I've spent the bet part of 2 hours trying to get this to work but can not seem to find a way to do this.
At the end of the day after a user has entered all data into a user data form I want the user to be able to push a button and let VBA determine if the request is for tomorrow based on todays day of the week (ie. Mon-Sat) and if so move/copy to sheet "Next Day"
However if it is not tomorrow's day then I want it to move/copy to another sheet "Future Request"

All data will be coming from a source sheet called wsData and the day of the week is the same column on each sheet being column A.
wsDest1 is Next Day worksheet

wsDest2 is Future Request worksheet

By using IF statements I suspect that it should be something similar to this:​

If wsData.Columns("1").value = Date+1 then copy to wsDest1
Else If [LEFT][COLOR=#222222][FONT=Verdana]wsData.Columns("1").value = Date>1 then copy to wsDest2

I will need to do some sort of similar copy/move back from the future request at a later date but the main thing is to get this 1st part working and I should then be able to work out the next.

Regards

Shane
[/FONT][/COLOR][/LEFT]

 

[austie]

Hi Joe,

I did look at the workday function but could not work out how it would capture the public holidays etc.
As the business operates Mon-Sat its really only the public holidays that cause a spanner in the works.

Regards

Shane

 

[Joe4]

As the business operates Mon-Sat its really only the public holidays that cause a spanner in the works.

Sounds like you need WORKDAY.INTL.
See: https://exceljet.net/excel-functions/excel-workday.intl-function

 

[austie]

Hi Joe,

Thanks for that I'll have a good look at that and then if have any further issues, I'll post another question.
For now with all your help so far I've got a basic working form for the organisation.

Now I just need to tweek it based on the work days etc.

Regards

Shane