Allocation of Tasks via VBA

DaveMy1978

New Member
Joined
Jul 1, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon All,

Hoping someone can help me.
I am working on setting up a simple Task List for a small group of people and would like to be able to 'assign' the task to the individuals task list.
The attached image shows the basic layout.

In essence I would like to go through the task list once a week, and assign a task to an 'owner' then once all tasks have been assigned, I can click the 'Assign Tasks' button, and the VBA will cut the row, and paste the row into the relevant persons Task List, i.e. all tasks for Dave go to Dave's list etc etc.
(I have managed to sort out the archive tasks at Least ;) but my VBA skills are very much basic, and I'm struggling with last bit)

Any help would be most appreciated.
 

Attachments

  • Task_List.png
    Task_List.png
    61.3 KB · Views: 56

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I think this would be a good solution to moving the tasks to the proper person's grouping:
VBA Code:
Sub AssignOut()

Dim i As Long
Dim srow As Integer
Dim lrowi As Integer
Dim Owner As String
Dim Osrow As Long
Dim Olrow As Long


srow = 7 'The starting row of your data (exclusing headers)
lrowi = Cells(srow, 2).End(xlDown).Row

For i = lrowi To srow Step -1
    Owner = Cells(i, 3)
    If Owner = "TBD" Or Owner = "" Then GoTo Nexti

    Osrow = Application.Match(Owner, Range("B:B"), 0) + 1
    Olrow = Cells(Osrow, 2).End(xlDown).Row

    Cells(Olrow + 1, 2).EntireRow.Insert shift:=xlDown
    Cells(Olrow + 1, 2) = "Assigned" 'You can change this to whatever you'd like
    Cells(Olrow + 1, 3) = Owner
    Cells(Olrow + 1, 6) = Cells(i, 6)
    Range(Cells(i, 2), Cells(i, 9)).Delete shift:=xlUp

Nexti:
Next i

End Sub
 
Upvote 0
Hi Max, and many thanks for your solution!
Works a charm!

Just wanted to ask, which line of code decides where to 'paste' the assigned task?
Reason I ask, is if a person starts with no tasks assigned, then the macro does not work correctly, (however providing the person has 2 tasks already assigned then it works flawlessly ;) )

Cheers
 
Upvote 0
I think this will fix that issue:
VBA Code:
Sub AssignOut()

Dim i As Long
Dim srow As Integer
Dim lrowi As Integer
Dim Owner As String
Dim Osrow As Long
Dim Olrow As Long


srow = 7 'The starting row of your data (exclusing headers)
lrowi = Cells(srow, 2).End(xlDown).Row

For i = lrowi To srow Step -1
    Owner = Cells(i, 3)
    If Owner = "TBD" Or Owner = "" Then GoTo Nexti

    Osrow = Application.Match(Owner, Range("B:B"), 0) + 1
    
    If Cells(Osrow, 2) = "" Then Olrow = Osrow - 1
    If Cells(Osrow + 1, 2) <> "" And Cells(Osrow, 2) <> "" Then Olrow = Cells(Osrow, 2).End(xlDown).Row
    If Cells(Osrow, 2) <> "" And Cells(Osrow + 1, 2) = "" Then Olrow = Osrow

    Cells(Olrow + 1, 2).EntireRow.Insert shift:=xlDown
    Cells(Olrow + 1, 2) = "Assigned" 'You can change this to whatever you'd like
    Cells(Olrow + 1, 3) = Owner
    Cells(Olrow + 1, 6) = Cells(i, 6)
    Range(Cells(i, 2), Cells(i, 9)).Delete shift:=xlUp

Nexti:
Next i

End Sub

End Sub



To answer your question, this is the "Paste" section:
VBA Code:
    Cells(Olrow + 1, 2) = "Assigned" 'You can change this to whatever you'd like
    Cells(Olrow + 1, 3) = Owner
    Cells(Olrow + 1, 6) = Cells(i, 6)
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top