Entire row auto copied into another datasheet if a value in a particular cell is 'yes'

[Nathalie_84]

Hi,

I have no idea if this is even possible, but I was wondering how I could ensure that a complete row of information is automatically tranferred into another datasheet, if one of the questions in that row is answered with 'yes'.

This is with regard to a form that I have set up to enter user registration info, which also involves several approval steps. Included are 4 important questions and there will be 4 seperate approval persons that I will be required to contact if the questions are answered with 'yes'. It would be ideal if the user information is automatically copied into a seperate datasheet that is designed for each particular approver so that they only see the info they are required to check. Is such a function possible within Excel?

Thanks in advance,
Nathalie

[mahaveersomani]

upload a sample workbook...

Regards
CA Mahaveer Somani

[Nathalie_84]

I have now included an attachment. I have only included somw of the questions but there will be more user Info in the actual File. If any ofthe fields are answered with 'yes', I would like that all of the User Information from that one line is automatically transferred into a seperate datasheet.

Many thanks again.

[:) Sixthsense :)]

Do right click on sheet tab and paste the below code and KEEP a sheet called Sheet2 for paste.

Please Login or Register  to view this content.

[Nathalie_84]

Thank you!

I have just tried editing the code but have been unsuccessful. So the scenario is as follows;


If 'yes' is answered in Column 'L', the information from that entire row should be copied to Datasheet 'Future Approval'
If 'yes' is answered in column 'O', the information from the entire row should be copied to Datasheet 'Adaptation Tool Approval'
If 'yes' is answered in column 'Q', the information from that entire row should be copied to Datasheet 'Planning Tool Approval'
If 'yes' is answered in column 'S', the information from that entire row should be copied to Datasheet 'Copy Approval'

In each datasheet, the table headers will start at B4 and the data input at B5.

Thanks in advance.

[:) Sixthsense :)]

Try this....

Private Sub Worksheet_Change(ByVal Target As Range) 
 
If Intersect(Target, Range("L:L,O:O,Q:Q,S:S")) Is Nothing Then Exit Sub 
If Trim(UCase(Target.Value)) <> "YES" Then Exit Sub 
Dim sWs As String 
 
Application.ScreenUpdating = False 
 
Select Case Target.Column 
    Case Is = 12: sWs = "Future Approval" 'ColumnL = 12 
    Case Is = 15: sWs = "Adaptation Tool Approval" 'ColumnO = 15 
    Case Is = 17: sWs = "Planning Tool Approval" 'ColumnQ = 17 
    Case Is = 19: sWs = "Copy Approval" 'ColumnS = 19 
End Select 
 
Target.EntireRow.Copy Sheets(sWs).Cells(Rows.Count, "B").End(xlUp).EntireRow(2) 
 
Application.ScreenUpdating = True 
 
End Sub