Copying data to another sheet based on criteria

[NeedExclHlp]

I have two sheets, one called Master and another called IEP. Data is entered into the Master sheet in rows from Column A to Column O.

If Column J has an "X", I would like Column E of the same row to be copied into the next available row in Column A of the IEP sheet. I also want Column F copied into Column B, Column C copied into Column C, and Column D copied into Column D.

I hope this makes sense. Any tips or suggestions would be greatly appreciated.

Thanks you.

 

[My Aswer Is This]

Try this:
This is a Module script and will not run automatically

You must have a upper case "X" in Column "J"

Sub needexclhlp()
'MAIT 8/30/16
Application.ScreenUpdating = False
Dim i As Long
Sheets("Master").Activate
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "j").End(xlUp).Row
Lastrowa = Sheets("IEP").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 1 To Lastrow
        If Cells(i, "J").Value = "X" Then
            Range("E" & i & ":F" & i).Copy Destination:=Sheets("IEP").Range("A" & Lastrowa)
            Range("C" & i & ":D" & i).Copy Destination:=Sheets("IEP").Range("C" & Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    
    Next
Application.ScreenUpdating = True
End Sub

 

[NeedExclHlp]

Thank you so much for your response. The macro runs perfect. However, is there a way to stop duplicates?

For example, if I add to Master sheet then Run Macro, it moves everything to IEP sheet.

If I add more to Master sheet, it moves the previous content again. So, I get duplicates on the IEP sheet.

Thank you again.

 

[My Aswer Is This]

The only way to solve this is to have these actions happen automatically when you put a "X" in column J.
To do that I will need to write a different script.

Would this work for you?

 

[NeedExclHlp]

Yes, please. That would work great. Thank you.

 

[My Aswer Is This]

Try this:

This is a auto sheet event script

Your Workbook must be Macro enabled

To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter a Uppercase "X" in column "J" your script will run

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J:J")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'MAIT 8/31/16 Mod 2
Application.ScreenUpdating = False
Dim ans As Long
Dim Lastrowa As Long
ans = Target.Row
Lastrowa = Sheets("IEP").Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Target.Value = "X" Then
            Range("E" & ans & ":" & "F" & ans).Copy Destination:=Sheets("IEP").Range("A" & Lastrowa)
        
            Range("C" & ans & ":" & "D" & ans).Copy Destination:=Sheets("IEP").Range("C" & Lastrowa)
            Cells(ans, 1).Offset(1).Select
        End If
    
Application.ScreenUpdating = True
End If
End Sub

 

[NeedExclHlp]

Thank you once again. It works great!

However, I have one more question. Can I do the same thing with different columns in the Master sheet?

The code now copies to the IEP sheet if the Master sheet has a X in Column J.

Can I also do below by adding to your code? Or would that be a totally different code?

On the Master sheet, if there is a X is in Column K, I would like it copied to the Legal Guardian sheet.
On the Master sheet, if there is a X is in Column H, I would like it copied to the Orphan sheet.
On the Master sheet, if there is a X is in Column I, I would like it copied to the GT Status sheet.
On the Master sheet, if there is a X is in Column L, I would like it copied to the Residency sheet.

Some in the Master sheet may be copied to multiple sheets. Also, the above sheets have the same columns as the IEP sheet.

Is this possible? Thank you so much for your time and effort.

 

[My Aswer Is This]

Yes this is possible.
Let me ask you a question.
Is there any column on the Master sheet that has the name of the sheet we want this row copied to.
For example on row three can I find the name of the sheet we want this row copied to.
If the sheet name we want this copied to was always in column "A" we could write the script to look in column "A" and copy the row to this sheet. We could actually do this without needing a X in column ?

We could tell the script to copy this row of data to the sheet name in column "A" when we double click on the sheet name.

I'll wait on your answer. If this is not possible I will write you a script to do as you asked in previous post.

If my double click option was possible this could apply to any new sheet names you may want to add later without need of modifying the code. You double click a sheet name and the active row is copied to the proper sheet.

 

[NeedExclHlp]

Thank you again for your response. We had a three day weekend so my response is delayed.

To answer your questions, there is no column that indicates the sheet. Below is the original Excel file I have been working with. I hope this helps.

https://drive.google.com/file/d/0B-naikWFPyq-bVRsQXZxbndMeGs/view?usp=sharing

Please let me know if you need anything else.

 

[My Aswer Is This]

Let me ask this question how does the user remember if he wants this row of data to go to the GT Status sheet he must put a X in column I ??
With you now up to J,K,H,I,L how does the user remember all this? And what result happens when he enters the X is some certain column.

I do not think I could remember all that? And if you continue adding more this could be difficult.
I think you need some visual reference some how. Like maybe the column header would give the sheet name. So if row(1) column (J) said "the IEP" and you put a X in column J they would Know this row of data is going to sheet "IEP"

I believe there is something going on here I do not understand.