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]

 

[Joe4]

There is a problem with this part:

If wsData.Columns("1").value = Date+1

It does not make sense to look at a whole column and see if it is equal to one specific value.
You either need to:
- Check for one particular cell in a range equal to some value
- Loop through your range and see which cells equal that value
- See if that value occurs anywhere in your range

Which of these three things are you actually trying to do with that formula?

 

[austie]

Hi Joe4,

I put that code in there as that is what I suspected it was meant to be but not sure.
What I am trying to do is loop through that column looking for anything that is tomorrow's day (i.e.. it's Tuesday here ATM so it should be looking for Wednesday) and move/copy that row to the

Next Day worksheet.
Anything that isn't tomorrow's day of the week (i.e.. Wednesday, Thursday etc) move/copy it to the worksheet

Future Request for future priority.

As this range will vary in number of rows the only non-changing aspect is the column this day of week will be in.

Regards

Shane​

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

 

[Joe4]

What I am trying to do is loop through that column looking for anything that is tomorrow's day

Yes, that is precisely what you would need to do (either use loops or filters). But you original code does neither of those things.

The following VBA code will move the rows, and delete the resulting blank rows on the original sheet:

Sub MyMoveRows()

    Dim wsData As Worksheet
    Dim wsDest1 As Worksheet
    Dim wsDest2 As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim diff As Long
    
'   Specify worksheets
    Set wsData = Sheets("Sheet1")
    Set wsDest1 = Sheets("Sheet2")
    Set wsDest2 = Sheets("Sheet3")
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A on data sheet
    lastRow = wsData.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 2
    For r = 2 To lastRow
'       Calculate difference between date entry and current date
        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
    Next r
    
'   Delete blank rows on data sheet (due to moves)
    wsData.Range("A1:A" & lastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    Application.ScreenUpdating = True
    
End Sub

 

[austie]

Hi Joe,

Thank you for this only part I'm not following in the code is the

[LEFT][COLOR=#333333][FONT=monospace]diff = wsData.Cells(r, "A") - Date[/FONT][/COLOR][/LEFT]

part.
Every time I run it comes up with an error stating "Compile error: Can't find project or library"

Essentially this is where I need it to check the date required and if greater than tomorrow (ie. 2 days or more) then move it to wsDest2 if tomorrow's date then move to wsDest1.
The date will always change so using system date/time would be ideal to keep it dynamic.

As for the looping I had that working via another VBA code, so I only posted what I was having issues with and not the whole code, sorry for that next time I'll remember to post the whole code

Regards

Shane

 

[Joe4]

Every time I run it comes up with an error stating "Compile error: Can't find project or library"

That line of code uses most of the same methods and functions as you did, so I see no reason why it would return that error. It is not using anything special.
Did you copy ALL of my code "as-is", or did you type it or change anything?

 

[austie]

Hi Joe,

Only thing I changed was the sheet names to match the name of the sheets I have I the file.
I've pasted it as I have it after that change.
If I knew how to upload the file I would so you could see what I'm trying to do.

Sorry I missed your questions in the earlier post.

Basicall what Im doing is taking a order process where the user enters a date in for when the delivery is required.
This stays in Column A regardless of the sheet. The data entry starts in cell A6 and will vary on how many orders are taken on the day.
then the search when user clicks on the end of day process will then search this row for the days next date and if found copy this to the spread sheet wsDest1.
Any other value found will then be copied to wsDest2 sheet.

Having re-visited some of the requirements I will be simplifying this for the end user as Public Holidays will impact the business and generation of this list.
So I will ideally create x6 buttons. Each one representing a day of the week that will then generate the next days job list.

Private Sub Testbtn_Click()    Dim wsData As Worksheet
    Dim wsDest1 As Worksheet
    Dim wsDest2 As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim diff As Long
    
'   Specify worksheets
    Set wsData = Sheets("New Job List")
    Set wsDest1 = Sheets("End Of Day Job List")
    Set wsDest2 = Sheets("Future Date")
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A on data sheet
    lastRow = wsData.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 2
    For r = 2 To lastRow
'       Calculate difference between date entry and current date
        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
    Next r
    
'   Delete blank rows on data sheet (due to moves)
    wsData.Range("A1:A" & lastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    Application.ScreenUpdating = True
    
End Sub

Thanks for your assistance so far too Joe

Regards
Shane

 

[Joe4]

If I knew how to upload the file I would so you could see what I'm trying to do.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

However, in this case, we may need to actually see your file. Many times, people will upload their files to a file sharing site like DropBox, and provide links to it. It is not something we typically recommend, because many people are unable or unwilling to download files (especially those with VBA code) from the internet for security reasons. I, myself, am unable to do it from work (my workplace strictly forbids it and blocks all file sharing sites). I can, however, from my home computer, though I won't be home until much later.

 

[austie]

Hi Joe,

Happy to what ever is best for you to get around my problem.
When taking screenshots I generally try to use the windows snipping tool and create jpeg file for use.

Again this is a process for an order form that the end user puts a requested date in
this then when they hit the add button will paste it into the excel spread sheet.

The column A contains the date converted to Day of week.
There is another column that has the date entered which is hidden. This can be any date.

But then when you hit the button to sort for this sheet it should look for anything that is the day of week (ie. Sunday, Monday etc) and then check this hidden column if the date is no greater than 7 days and copy it to the

wsDest1.
If the date is greater than the date of today +7 it copies the files to the

wsDest2 sheet.

I can change the column references and the sheet references no problem I just can't get the conditioning code right despite all that I have done.

Regards

Shane​

​

 

[Joe4]

I am working from home today, so if you could upload your file and provide a link, that would be great.
I think in this situation it would be best to see the file, as you appear to be getting errors where you shouldn't.