Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one Sheet and paste on another sheet based on condition
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one Sheet and paste on another sheet based on condition
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 "Prem" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one Sheet and paste on another sheet based on condit
Hi Joel,
Thanks a lot for your help. You really made my Christmas tesnsion free. Wish you very happy and merry Christmas. Regards Prem "Joel" wrote: 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 "Prem" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Copy and Paste based on Condition | Excel Discussion (Misc queries) | |||
copy data to another sheet on condition | Excel Worksheet Functions | |||
Copy certain cells from one sheet to another with a condition | Excel Worksheet Functions | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel |