Auto Filter on the next business date

Hi All,

I have a worksheet with huge data, where a column has many dates, and I have to filter & copy the data according to the next working date.
I've written below codes, but that works for the next date instead of the next business date. What if some other day is official holiday, then the data should be auto filtered as per the next business date/day (Monday to Friday).

How can I have a userform, which will ask the date in a specific format for auto filtering the data, then perform the written codes on that date in the worksheet? I think this is how I would get the accuracy all the time without any wrong inputs.

Here below, if I use a date as 05/15/2011 or 05/15/11 instead of 5/15/2011 then auto-filter doesn't work properly. so I need a correct format of the input date.

Please Login or Register  to view this content.

Please see the 'userform' in the VBA code window (ALT+F11). I am sure that someone will come up with the solution & help me out.

Workbook: MS Excel 2007

Thanks in advance!

Hi,

Here is some code for the "NextBusDay"
Pace it in the userform code module.

Please Login or Register  to view this content.

For the Holidays you need to know what the Holidays are and in some way reference to it.
I hope this code will help.

Thanks for ur quick reply!

If you see the above code, where I get the solution through an InputBox. I have to provide it a specific future date, and it works fine, BUT I need to do run the above activity through a userform. I have to write some additional information on the userform, which can't be displayed on an InputBox msg.

In the userform, if somebody writes date in any format, then the date format should be the same as the dates in the worksheet for auto-filter

...so please help me out. If you see below the attached you can understand the userform.

Thanks in advance!

SunOffice,

Check this attachment. I removed the Properties for textbox1. and too the code for textbox1 click. I left the code in Module 1 but moved and modified it for the userform "Ok" button. I tested it and it worked for me.

Thanks for your efforts and support for me! You are such a great personality.

I'm so thankful to you for understanding my need of the vba codes.
I just tried ur codes, but somewhere it is missing the data. Sometimes it misses the values to pull-out and copy. If you run the my codes(input box method), are running well, BUT I think the DATE format should be taken care before all of the activity.

You can see, where I am getting problem, If today is 05/20/2011 then if I run the codes via userform for a future date:
- doesn't perform 'No Response' activity for each defined future date.
- If I don't provide any dates in the userform, then also it runs on clicking 'OK'.
- If I write date as 05/21/11, then the codes run, but doesn't work properly.
- Everytime I would like to display the current date in the userform, so whenever one runs the codes; the userform should be loaded with the current date in the Textbox (like the inputbox shows the current date at the time of asking the future date)

Request you to please provide me the solutions.

Thanks in advance!

SunOffice,

I modified the code. It now shows the current date in the text box and too when I ran your code and the code for the Option button the data displayed in the "GetData" sheet are the same. The only thing I changed was set the Text box to todays date and I removed the "Userform.Hide" in the Sub for the CommandButton1 click.

Yeah I just edited the format of dates in the userform code, and it's also working fine for me.... WOW!!

Thanks a world Mr Charles, you are really a genius.

Please Login or Register  to view this content.