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.
If you want to start to use the software as soon as possible, then you can:
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.
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.
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.
Leave a Reply