How to Easily Manage, Track and Split the Tasks of Your Project in Excel

If you are a project manager, this article will help you to easily plan and track tasks in your project and instantly assign a task to your employees. Split the master file and share it with each employee.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

For this tool, we need a macro enabled workbook with 2 sheets. Rename sheet1 as “Task” and Sheet2 as “ControlPanel”. On sheet “Task” create headers “Task No”, “Type”, “Task Description”, “Assigned to” and “Status”. Using shapes, create a button and name it as “Share Tasks”

On Sheet “ControlPanel”, create headers “Reportee Names”, “Type” and “Status”.

Let’s prepare the database

The sheet “Control Panel” will hold all values for our drop down. We can create dynamic named ranges referring to each header on the sheet “Control Panel”. These dynamic named ranges can be used for creating dropdowns on the sheet “Tasks”. These drop down would help us to quickly pick an employee, change the status or type of the task. Prepare the sheet “Task”.

Let’s make it functional

Copy the macro to a new module in your macro enabled workbook. Assign this macro to the button we have created on the sheet “Task”.

Sub Split_Tasks()
    Dim r As Long
    Dim lr As Long
    lr = Sheets("ControlPanel").Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        On Error Resume Next
        ThisWorkbook.Sheets("Temp").Delete
        Sheets.Add.Name = "Temp"
        On Error GoTo 0
        Sheets("Task").Range("A3:E3").Copy _
        Destination:=Sheets("Temp").Range("A3")
        Dim lr2 As Long
        Dim r2 As Long
        lr2 = Sheets("Task").Range("A" & Rows.Count).End(xlUp).Row
        For r2 = 4 To lr2
            If Sheets("Task").Range("D" & r2).Text = Sheets("ControlPanel").Range("A" & r).Text Then
                Dim lrint As Long
                lrint = Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row + 1
                Sheets("Task").Range("A" & r2 & ":E" & r2).Copy _
                Destination:=Sheets("Temp").Range("A" & lrint)
            End If
        Next r2
            Dim wb As Workbook
            Dim ab As Workbook
            Set ab = ThisWorkbook
            Sheets("Temp").Copy
            Set wb = ActiveWorkbook
            With wb
                .SaveAs ab.Path & "\" & ab.Sheets("ControlPanel").Range("A" & r).Text & Format(Date, "yyyymmdd")
                .Close True
            End With
            ab.Activate
    Next r
            On Error Resume Next
            Sheets("Temp").Delete
            On Error GoTo 0
End Sub

How does it work?

The macro reads all the employee’s names on the sheet “ControlPanel” and for each employee, all rows on the sheet “Task” are scanned. If the employee name in a row matches with the employee name on the sheet “ControlPanel”, the row is copied along with headers to a new sheet “Temp”. When all rows for an employee has been copied to the sheet “Temp”, this sheets gets saved in the same root folder as a new workbook in the name of the employee and the date of report generation.

Next step

You can modify the script to automatically email files to the employee using Gmail or outlook. After all the files are updated by the employees, they can be placed in the same master folder and the macro reads all the files and updates the status in the sheet “Task”. A damaged Excel file cannot be opened or read from a folder. Delete the corrupted file so that the macro can pick other normal files.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word repair and outlook recovery software products. For more information visit www.datanumen.com.