Formula to extract info or VBA code

[rama4672]

I have got a work book with 7 sheets in them named sunday to saturday, on the sheets is a list of drivers with their start and finish times it is not always the same start time for the driver every day and he may not be on every day,now what i need to do is to create a sheet name it wages and then look at each day get the drivers start time and finish time and copy it to the wages sheet building a rota,whith all the drivers on it and what they worked all week.
This could be either done by inputting formulas into the wages sheet or entirly with vba, which ever is the fastest to run.

Thank you to any one who can help with this

Ian

[Jacob Hilderbrand]

You could probably use the VlookUp function to pull the data, or it could be gathered with VBA. Can you post an attachment so we can get a better idea of what you are dealing with?

[rama4672]

Thanks for the fast reply DRJ


Here is the attachment, But a bit of how it works.
When it starts up it goes to a menu page, just hit transfer full rota and then it will put the drivers in what days they are working through the week, then once they have finished the shift a finish time is entered on the relevent day and at the end of the week i need to collect all this information.

Thanks


Ian

[Jacob Hilderbrand]

The file is missing your DatePicker Control so I can't run the macro...

[rama4672]

HI Jake

What do you mean it is missing the date picker control?

AS that is week 8 of an actual rota that we are using.

Regards

Ian

[Jacob Hilderbrand]

When I open the attachment I get an error that it could not load an object. When I try to run Transfer Full Rota I get an error on the UserForm Initialize for Me.DTPicker1 = Date and there is no DTPicker1 on the User Form.

There is data on the Rota sheet but nothing on Daily or Sunday-Saturday. Or are we just working with data from the Rota sheet?

[rama4672]

Thanks for the reply Jake

Here is the file again once the data has been transfered.
It uses tha data that is on the rota sheet, the daily sheet is only used to manipulate all the data and then it copies it to the relevent days.

Also if you press any of the buttons on the menu labled Monday through to saturday it does the same apart from not putting the date on the sheets, and it only starts the rota from monday not sunday.
I hope this helps you.
I have attached a file to show what it looks like once it has been transfered just in case you are still having trouble running it.
The information that is on every sheet this is the information that i need to get and list it all on one sheet at the end of the week,once it has done that the last sheet which should be named wages should look the same as the rota sheet but in alphabetical order with the actual finish times instead of the rotad finish times.


Regards

Ian

[Jacob Hilderbrand]

Try this.

Option Explicit

Sub CompileData(WS As Worksheet, Col As Long)
Dim i               As Long
Dim LastRow         As Long
Dim Cel             As Range
With WS
        LastRow = .Range("A65536").End(xlUp).Row
        For i = 9 To LastRow
            Set Cel = Sheets("Wages").Range("A:A").Find( _
            What:=WS.Range("A" & i).Value, _
            LookIn:=xlValues, LookAt:=xlWhole)
            If Not Cel Is Nothing Then
                Sheets("Wages").Range(Cells(Cel.Row, Col).Address).Value = _
                WS.Range(Cells(i, 6).Address).Value
                Sheets("Wages").Range(Cells(Cel.Row, Col + 1).Address).Value = _
                WS.Range(Cells(i, 7).Address).Value
            End If
        Next i
    End With
End Sub

Sub Macro1()
Application.ScreenUpdating = False
    Application.EnableEvents = False
Sheets("Wages").Range("D10:IV65536").clearcontents
    Call CompileData(Sheets("Sunday"), 6)
    Call CompileData(Sheets("Monday"), 8)
    Call CompileData(Sheets("Tuesday"), 10)
    Call CompileData(Sheets("Wednesday"), 12)
    Call CompileData(Sheets("Thursday"), 14)
    Call CompileData(Sheets("Friday"), 16)
    Call CompileData(Sheets("Saturday"), 18)
Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

I made a sheet called Wages and copied the layout and employee data from ROTA. See the attachment for more information.

[rama4672]

Thank you for that Jake,
It works great, but i also need it to show what shift they are on ie: 8 hour or 10 hour shifts.
And if they are on hol, off or sick, if i place the off, hol or sick in the same col as the start and finish times it works ok.
Also if i add another name on to the rota would it still pick it up

Regards

Ian

[Jacob Hilderbrand]

To add in the shift, just add another line to the code to add that value.

Option Explicit

Sub CompileData(WS As Worksheet, Col As Long)
Dim i               As Long
Dim LastRow         As Long
Dim Cel             As Range
With WS
        LastRow = .Range("A65536").End(xlUp).Row
        For i = 9 To LastRow
            Set Cel = Sheets("Wages").Range("A:A").Find( _
            What:=WS.Range("A" & i).Value, _
            LookIn:=xlValues, LookAt:=xlWhole)
            If Not Cel Is Nothing Then
Sheets("Wages").Range(Cells(Cel.Row, 5).Address).Value = _
                 WS.Range(Cells(i, 5).Address).Value
Sheets("Wages").Range(Cells(Cel.Row, Col).Address).Value = _
                WS.Range(Cells(i, 6).Address).Value
                Sheets("Wages").Range(Cells(Cel.Row, Col + 1).Address).Value = _
                WS.Range(Cells(i, 7).Address).Value
            End If
        Next i
    End With
End Sub

You can do something if they are on Holiday or Off by checking that cell.

Select Case WS.Range("D" & i).Text
Case Is = "Hol"
'Do Something
Case Is = "Off"
'Do Something Else
Case Else
End Select

The names need to be added to the Wages sheet as it is not setup to automatically look at the ROTA sheet.

[rama4672]

Thank You for that jake

I think I can safely say this is solved, that is untill they decide there is something else that they would like it to do.

Regards

Ian

[Jacob Hilderbrand]

You're Welcome

Take Care

[rama4672]

Hi Jake

I am back again, i thought this was solved, but i have come accross a problem, when it gets the shift from col d and puts the value in to the wages sheet it is ok for sunday it gets them all, but when it goes to monday if they were on shift on sunday but off on monday it delets the value that was in the col d.
Will i need it to check if the cell isblank before writing to it if it is then it can write if not leave it as it is.
It seems to do that for them all, so it only displays the shift if they are working on the last day ie:saturday.

Thanks

Regards

Ian

[Jacob Hilderbrand]

There was only room for one shift on your summary report. So what if they worked one shift on Monday and a different shift on Tuesday?

You can check if the Shift cell already has a value, and if so don't change it.
[vba]
Option Explicit

Sub CompileData(WS As Worksheet, Col As Long)

Dim i As Long
Dim LastRow As Long
Dim Cel As Range

With WS
LastRow = .Range("A65536").End(xlUp).Row
For i = 9 To LastRow
Set Cel = Sheets("Wages").Range("A:A").Find( _
What:=WS.Range("A" & i).Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not Cel Is Nothing Then

If Sheets("Wages").Range(Cells(Cel.Row, 5).Address).Value <> "" Then
Sheets("Wages").Range(Cells(Cel.Row, 5).Address).Value = _
WS.Range(Cells(i, 5).Address).Value
End If

Sheets("Wages").Range(Cells(Cel.Row, Col).Address).Value = _
WS.Range(Cells(i, 6).Address).Value

Sheets("Wages").Range(Cells(Cel.Row, Col + 1).Address).Value = _
WS.Range(Cells(i, 7).Address).Value

End If
Next i
End With

End Sub
[/vba]

[rama4672]

Thank you for the quick reply Jake

As to your question about the shifts, if they start the week on 8 Hour shift they are on them all week, the same goes for the 10 hour shifts.

I had to change a bit of the code to check for a value in the shift col,
[VBA]If Sheets("Wages").Range(Cells(Cel.Row, 5).Address).Value <> "" Then[/VBA]
I changed to
[VBA]If Sheets("Wages").Range(Cells(Cel.Row, 5).Address).Value = "" Then[/VBA]
as when i ran the first part it left the col blank

Thanks again
Regards

Ian

[Jacob Hilderbrand]

Ok, I put it backwards by mistake. Basically, If the value is "" then add the new value, otherwise leave it alone.

Take Care