Copy from one Sheet and paste on another sheet based on condition

P

Prem

Hi,

I have an excel sheet where on the Sheet 1 I have two columns (Resource Name
and Task Assigned). And there are other sheets on the name of resources e.g.
James, Williams. In the "James" sheet I have first column as Task Assigned"
and then there are other columns. Similarly I have 8 similar sheets.

My question is if in the Main Sheet I enter Resource Name (e.g. James) and
Task Assigned as YYY. Then this task "YYY" should get pasted in the task
column of James. And if I enter William in Resource Name column then the task
assigned should get pasted in task column of sheet "William".

I appreciate your help.
 
J

Joel

You need to havve a workshhet change function like the one below. the code
check if bot column a and colmn b has data before it makes an entry. it
doesn't remove an entry if a cell is changed.

Sub worksheet_change(ByVal Target As Range)

'ResourceName
If Target.Column = 1 Then
If Target <> "" And _
Target.Offset(0, 1) <> "" Then

With Sheets(Target.Value)
If .Range("A1") <> "" Then
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Else
NewRow = 1
End If
.Range("A" & NewRow) = Target.Offset(0, 1)
End With
End If
End If
'Task
If Target.Column = 2 Then
If Target <> "" And _
Target.Offset(0, -1) <> "" Then

With Sheets(Target.Offset(0, -1).Value)
If .Range("A1") <> "" Then
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Else
NewRow = 1
End If
.Range("A" & NewRow) = Target
End With
End If
End If

End Sub
 
P

Prem

Hi Joel,

Thanks a lot for your help. You really made my Christmas tesnsion free. Wish
you very happy and merry Christmas.

Regards

Prem
 
Top