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]

Good Morning Joe,

As requested here is the file as requested.

https://1drv.ms/x/s!AjGLXobcYAvqk2FcLxlpDm5I9znZ

I was able to get it to copy the rows from sheet "New Job List" to "End Of Day Job List" via the "End of day sort" button.
The only problem is I need to specify the day in the code for it to copy the row which is ok, but I need it to copy anything that is not the day listed to the "Future Date" sheet also.

The way that it is meant to sort is from todays system date less than 7 days goes to the

"End Of Day Job List" anything that is greater than 7 days from

todays system date will go to the

"Future Date" sheet.

I then need to have the sort to check the

"Future Date" sheet and move any items

less than 7 days from

todays system date also to the

"End Of Day Job List".

I know that there is an easier way to do this but this is the best I can do.

Thanks again for all your help Joe.

Regards

Shane​

​

​

​

_{^{<strike>
</strike>}}​

​

​

 

[Joe4]

OK. I am back in the office today, so won't be able to download the file until tonight when I am at home.

 

[Joe4]

Do you have a version of the file with sample data that I can use for testing?
It looks like the "New Job List" sheet has no data on it.

 

[austie]

Hi Joe,

As I am just trying to get the sheet working there is no sample data to be used.
I just create via the Job list button creating records.
The date, time, delivery address, product, product type & amount are all mandatory fields in the user form (all items are indicated in red)

So feel free to create anything as my data is usally just test, mcfly etc with different dates and times.

Regards

Shane

 

[Joe4]

Sorry, I have not had a chance to dive into this yet. My nights have been booked pretty solid the past few nights.
Originally, I had requested the file because you said that you were getting "Compiler" errors, but I do not see any more mention of those, so I assume that those are no longer a problem?
I did not get any compiler errors when I downloaded your file.

It looks like you have new questions since the compiler issues, so I need to revisit and re-read the whole thread and dive into that. I *should* have time to do that tonight.

 

[Joe4]

I tested the Testbtn_Click code and it seems to work fine for me with one minor modification. I needed to change this line:

    For r = 6 To lastRow

to this:

    For r = 3 To lastRow

Note that it appears that the first blank row of your data sheet where you can enter data is row 3, not row 6.

Also, in an earlier post, your were talking about taking holidays into account. You can take holidays and weekends into account with the Workday function.
Here is a description of how you can use it in VBA: http://codevba.com/excel/WorksheetFunction/WorkDay.htm#.Wyr_EVVKiM8

 

[austie]

Hi Joe,

Thanks for looking into this very much appriciated.
The compiler error (sorry my bad I forgot to advise) I was able to get rid of after I found out it was caused by the upgrade of equipment from Office x86 to office x64 version and disabling the date picker dll.

The problem that I still have is that I basically need to sort the items by day/date into the various sheets.
I've updated the spreadsheet with a range of dummy dates.

https://1drv.ms/x/s!AjGLXobcYAvqk2FcLxlpDm5I9znZ​

Basically I want a user to select a day (this is the part I'm not sure best way to do either individual button or a prompt for the day)
This will then go through the sheet "New Job Sheet" and copy any rows that are not greater than +7 from todays date for that day, to the

sheet "​

End Of Day Job List

".
Any rows that are greater than +7 from todays date will copy to sheet "Future Date" for future records.

The end of day sort button should also look at the

sheet "​

future date" and move any rows that

are not greater than +7 from todays date for that day, to the sheet "End Of Day Job List", but leave all other rows on there.​

​

I've worked out how to copy the rows to the sheet for a singular day depending on what has been coded to the day, but not to do dual copies to different sheets.

Row 6 is the 1st row of the table for the 3 sheets "New Job List",

"End Of Day Job List" & "Future Date" as they will eventually when finished with this have a header in the rows 1-3 for the final print out.

Let me know if I can provide any further details.

Regards

Shane​

 

[Joe4]

I am sorry, I am having a hard time understanding/following what you are trying to do. It seems a bit different than the original question you posted where you were just talking about two days.
First, off if you are working with different days (7 days from current date or whatever), I think it would be much better to display your dates as actual days, and not days of the week. Otherwise, you cannot really tell if it is Friday of this week, or next, or the following, etc.

Regarding moving records greater than 7 days, note that at the beginning of the question, we were calculating the difference between the date and the current date to see if it was more than 1, i.e.

        diff = wsData.Cells(r, "A") - Date
'       Decide if to move row
        Select Case diff
            Case 1
                wsData.Rows(r).Cut wsDest1.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Case Is > 1
                wsData.Rows(r).Cut wsDest2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End Select

If you instead wanted to check for records greater than 7 days, you could do something like:

        Select Case diff
            Case Is > 7
                'do this if difference greater than 7 days
            Case Else
                'otherwise, do this
        End Select

 

[austie]

Hi Joe,

Sorry for not getting back to you, sooner I've been away unwell.
This form is basically an order form that instead of the staff writting out by hand and then entering into a spreadsheet at the end of the day they can add to it daily and the macro will sort/filter automatically for them.
End of the day job list is basically going to be today's date +1 and then they can modify/edit as they see fit.

This was the model that I was working towards however unfortuately I forgot to factor in public holidays and as such the +1 model won't work. This is why I'm now going to be basing it off a day of the week and then having the date field be less than 7 days so it gets the next day of the week. ie. Today is Monday so I want to create any jobs for Tuesday but if someone orders something for tuesday next week I don't want that to be populated until next Tuesday.

With the sorting any thing that is greater than 7 days will be moved to the future date sheet to be re-arranged/sorted at another time (ie. this process will be repeated to bring any requests to the end of day list if it's basically today's date +1 otherwise leave where it is)


Unless you have a better way to accomplish what I'm trying to accomplish I'm all ears so to speak on how to do this.

Regards

Shane

 

[Joe4]

Have you looked at the Workday function (this is the third time I am mentioning it), which finds the NEXT workday, based on a certain date and takes weekends and holidays into account?