Work Distribution Macro

[Chefsohail]

Hi Team,

I am currently managing a team of people and I am facing challenges in distributing the work. Its taking time and I plan to automate this function.

I am seeking your expertise here -

Pre-requisite info
1. I have data that starts from Column A until MN. (this is for now and it may increase in future)
2. The row entries is something that I do not have control on. It can either be 200 or sky is the limit. The macro file should help me with distributing the workload evenly.
3. Eg: So my first row has headers and I have 5000 rows filled (headers are filled from Column A through MN)....(Column A will have all the 5000 cells filled.. but this may not be the case with other columns..as this involves the job and the team member is expected to fill the other columns)

Expectation from the macro.
1. This file should have a sheet where i can paste all my data.. (whether it be 1 row or 1,00,000 rows from column A - column MN and may be more in future)
2. The workbook should have a database for my list of employees and this should be expandable so that i can keep modifying the strength as the case may be. Like the table below.

3. When I execute the macro, the macro should assign tasks to only 2 people as their status is active.
4. The system should then start distributing the work and create separate files that get saved to a folder on my desktop. Folder named as - 'Allocation_ddmmyyyy'.
Eg: If I have 500 tasks to be distributed - (exclude the header row) amongst 5 team members, then i copy and paste this data from column A - column MN in the macro enabled sheet. Once i execute it, the system picks up first 100 tasks, creates a new file and copy pastes the first 100 rows from Column A through MN (or may be more in future)(Here i may have formatting eg: backgrounds / bold/ italics etc or data validation - all need to be copied). It then saves this file in folder 'Allocation_ddmmyyyy' as 'Allocation_ddmmyyyy_Employee1' and so on. So basically the folder should have 5 excel workbooks individually named as Allocation_ddmmyyyy_Employee1, Allocation_ddmmyyyy_Employee2, Allocation_ddmmyyyy_Employee3 .... and so on.

This is my first requirement.

4. If we can also add a separate macro where these file can be automatically emailed to the user.

so now the above job is done and the macro sends an email to the user that this is his work allocation for the day for now and the corresponding file should be attached on that email.
I can call these macros basis my requirement for the day.

I am pretty confident that this is doable, but I do not know how to do it. If someone can please help, that'll be great.

Please do let me know if any other information is required. I'll be glad to share.

Best Regards.

 

[fadee2]

hi chefsohail,
I'd suggest uploading a sample file with some sample data to any free file sharing service like google drive or dropbox and pasting the link in the thread. This would make it easier for everyone trying to help in suggesting a solution, rather than making assumptions and creating their own data.

 

[Chefsohail]

hi chefsohail,
I'd suggest uploading a sample file with some sample data to any free file sharing service like google drive or dropbox and pasting the link in the thread. This would make it easier for everyone trying to help in suggesting a solution, rather than making assumptions and creating their own data.

Hi Fadee2,

Thanx for writing.

Please find the attached sample sheet for your ease.

chefsohail.xlsx

drive.google.com

The attached file has 2 sheets.
1. RawData
2. EmployeeDB

Initially my RawData sheet will be blank. I will copy and paste all my work here as already added the sample data.
Sheet EmployeeDB will have the details as given.

I am currently away from my machine and have managed to create one on my phone. Hope it helps.

 

[mumps]

How do you determine how many tasks you want to distribute? Once that has been determined, you want to distribute the tasks equally to all the active "Active" employees. The file you posted has 6 active employees. Is this correct?

 

[Chefsohail]

How do you determine how many tasks you want to distribute? Once that has been determined, you want to distribute the tasks equally to all the active "Active" employees. The file you posted has 6 active employees. Is this correct?

Thanx Mumps,

Q. How do you determine how many tasks you want to distribute?
It's the inflow of task on a daily basis. Sometimes 500, sometimes 5000 n sometimes 20,000. There is no fixed number.

Q. Once that has been determined, you want to distribute the tasks equally to all the active "Active" employees?
Yes.

Q. The file you posted has 6 active employees. Is this correct?
Yes.. However the count will change daily based on attendance. Also my current team headcount is 20 which is expected to move to 120.

 

[Chefsohail]

Thanx Mumps,

Q. How do you determine how many tasks you want to distribute?
It's the inflow of task on a daily basis. Sometimes 500, sometimes 5000 n sometimes 20,000. There is no fixed number.

Q. Once that has been determined, you want to distribute the tasks equally to all the active "Active" employees?
Yes.

Q. The file you posted has 6 active employees. Is this correct?
Yes.. However the count will change daily based on attendance. Also my current team headcount is 20 which is expected to move to 120.

So basically I want to distribute all the tasks in the sheet

 

[mumps]

This macro should do everything except send the email. Make sure that the folder 'Allocation_ddmmyyyy' exists on your desktop and try the macro. Do you want the same macro to also automatically send the email or do you want to do that separately after the new files have been created and saved? What would be the subject and body of the email?

Sub CreateFiles()
    Application.ScreenUpdating = False
    Dim arr As Variant, lRow As Long, i As Long
    With Sheets("Employee DB")
        .Range("B2").CurrentRegion.AutoFilter 3, "Active"
        .Range("B3", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy .Range("F1")
        .Range("B2").AutoFilter
        arr = .Range("F1", .Range("F" & Rows.Count).End(xlUp)).Value
        .Columns(6).ClearContents
    End With
    With Sheets("RawData")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Columns("A").Insert
        Range("A1") = "Emp"
        With .Range("A2:A" & lRow)
            If .Rows.Count <= UBound(arr, 1) Then
                .Value = arr
            Else
                .Resize(UBound(arr, 1)).Value = arr
                .Resize(UBound(arr, 1)).AutoFill .Resize(.Rows.Count)
            End If
        End With
        For i = LBound(arr) To UBound(arr)
            .Range("A1").CurrentRegion.AutoFilter 1, arr(i, 1)
            .AutoFilter.Range.Copy
            Workbooks.Add
            With ActiveSheet
                .PasteSpecial
                .Columns(1).Delete
                .Name = arr(i, 1)
            End With
            With ActiveWorkbook
                .SaveAs Filename:=Environ("userprofile") & "\Desktop\Allocation_" & Format(Date, "ddmmyyyy") & "\Allocation_" & Format(Date, "ddmmyyyy") & "_" & arr(i, 1) & ".xlsx", FileFormat:=51
                .Close False
            End With
        Next i
        .Columns(1).Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Chefsohail]

Hi Mumps,

Thanks for the code. It works. I sincerely appreciate the effort.

There's a glitch that I am experiencing. If I leave the attendance in the EmployeeDB as is i.e Employee1, 2, 4, 5, 9 and 10 to be active. It then equally distributes the workload. However when i change the attendance, it only allocates 1 task to the active employee. That is my first test yet. Please let me know if I am doing anything incorrectly.

This macro should do everything except send the email. Make sure that the folder 'Allocation_ddmmyyyy' exists on your desktop and try the macro. Do you want the same macro to also automatically send the email or do you want to do that separately after the new files have been created and saved? What would be the subject and body of the email?

Sub CreateFiles()
    Application.ScreenUpdating = False
    Dim arr As Variant, lRow As Long, i As Long
    With Sheets("Employee DB")
        .Range("B2").CurrentRegion.AutoFilter 3, "Active"
        .Range("B3", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy .Range("F1")
        .Range("B2").AutoFilter
        arr = .Range("F1", .Range("F" & Rows.Count).End(xlUp)).Value
        .Columns(6).ClearContents
    End With
    With Sheets("RawData")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Columns("A").Insert
        Range("A1") = "Emp"
        With .Range("A2:A" & lRow)
            If .Rows.Count <= UBound(arr, 1) Then
                .Value = arr
            Else
                .Resize(UBound(arr, 1)).Value = arr
                .Resize(UBound(arr, 1)).AutoFill .Resize(.Rows.Count)
            End If
        End With
        For i = LBound(arr) To UBound(arr)
            .Range("A1").CurrentRegion.AutoFilter 1, arr(i, 1)
            .AutoFilter.Range.Copy
            Workbooks.Add
            With ActiveSheet
                .PasteSpecial
                .Columns(1).Delete
                .Name = arr(i, 1)
            End With
            With ActiveWorkbook
                .SaveAs Filename:=Environ("userprofile") & "\Desktop\Allocation_" & Format(Date, "ddmmyyyy") & "\Allocation_" & Format(Date, "ddmmyyyy") & "_" & arr(i, 1) & ".xlsx", FileFormat:=51
                .Close False
            End With
        Next i
        .Columns(1).Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Chefsohail]

Hi Mumps,

Thanks for the code. It works. I sincerely appreciate the effort.

There's a glitch that I am experiencing. If I leave the attendance in the EmployeeDB as is i.e Employee1, 2, 4, 5, 9 and 10 to be active. It then equally distributes the workload. However when i change the attendance, it only allocates 1 task to the active employee. That is my first test yet. Please let me know if I am doing anything incorrectly.

Hi Mumps,

i tried running it again. The behaviour is weired, sometime it works.. Sometime it doesnt...

 

[mumps]

I've tried it several times and I can't reproduce the problem. Are you testing the macro on the file you posted or on a different file? If on a different file, can you upload a copy of the actual file, de-sensitized if necessary? I wouldn't need all the data rows, just enough rows to test the macro. Could you answer my questions about how you want to handle the email (Post #7)? Also when replying, please click the "Reply" button instead of the "+Quote" button to avoid clutter.