Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search for rows in one sheet and copy into another sheet based on customer id [email protected] Excel Worksheet Functions 1 October 22nd 07 03:09 AM
Copy and Paste based on Condition EJ Excel Discussion (Misc queries) 1 June 27th 07 11:17 PM
copy data to another sheet on condition vijaydsk1970 Excel Worksheet Functions 1 March 22nd 07 05:12 AM
Copy certain cells from one sheet to another with a condition [email protected] Excel Worksheet Functions 1 January 4th 07 12:08 AM
Active Cell Copy And Paste Sheet to Sheet A.R.J Allan Jefferys New Users to Excel 4 May 4th 06 02:04 AM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"