copy cells based on cell value

[Nichole09]

Hello All,


Hoping someone can help me, I have tried several codes and searching the internet and I feel my request is not complicated but I cant seem to figure it out. I want a worksheet_change macro that checks column H on my worksheet("Tracker") for values. If column H equals either "Purchased" or "Closed" I want most of that row (columns A through F, skip G, copy H) to copy to another sheet titled ("Closed Loans"). The part I am struggling with the most (struggling with the whole thing, but mostly), is having these values paste on my "closed loans" sheet down a row if data is already in the row before it. After copying/pasting the values in my "Tracker" sheet, I would like this entire row to be deleted. I don't know if it makes a difference, but I want to mention a few things, the value to search for in column H is from a data validation list. Also, the rows to copy from are in a table, and they will be pasted on sheet "Closed loans" to another table.

Some progress? I know this code is not finished but I am stuck on where to go from here...

Sub worksheet_change(ByVal target As range)
If Not Intersect(target, range("H2:H200")) Is Nothing Then
Application.ScreenUpdating = False
Dim cell As range
For Each cell In range("H2:H200")
If cell.Value = "Purchased" Or "Closed" Then
For i = 1 To 6
lastrow = Sheets("Closed Loans").Cells(Rows.Count, i).End(xlUp).Row + 1

 

[My Aswer Is This]

This sheet change event activates when a value is changed in Column H
This script does not loop through all the rows but only the row that has just changed in column M

This is an 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

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-23-18 1:30 AM EDT
If Not Intersect(Target, Range("H:H")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
Dim Lastrow As Long
    If Target.Value = "Purchased" Or Target.Value = "Closed" Then
        Lastrow = Sheets("Closed Loans").Cells(Rows.Count, "A").End(xlUp).Row + 1
        ans = Target.Row
        Cells(ans, 1).Resize(, 6).Copy Sheets("Closed Loans").Cells(Lastrow, 1)
        Cells(ans, "H").Copy Sheets("Closed Loans").Cells(Lastrow, "H")
        Rows(ans).Delete
    End If
End If
End Sub

 

[Nichole09]

Thank you for your response and help on this one. I tried your code, but all it seems to do is create an H column on the "Closed Loans" sheet. It does not copy over the row or any data. ??

 

[My Aswer Is This]

I test all my scripts.

The script looks in column H for the value

Purchased or Closed

If you enter either of these values in column H the entire row of data will be copied to sheet named
Closed Loans.

Now you must enter these values exactly as you see them in the script

See this line of the script:

If Target.Value = "Purchased" Or Target.Value = "Closed" Then

Did you put the script in the sheet with your data like I explained?

This script only runs when you enter those values in column H
It will not loop through all previously entered data entered in column H

Try this on a empty sheet. Enter the values mentioned in column H and see what happens.

 

[My Aswer Is This]

I do not know how the script could as you said:

but all it seems to do is create an H ​

column

 

[Nichole09]

I test all my scripts.

The script looks in column H for the value

Purchased or Closed

If you enter either of these values in column H the entire row of data will be copied to sheet named
Closed Loans.

Now you must enter these values exactly as you see them in the script

See this line of the script:

If Target.Value = "Purchased" Or Target.Value = "Closed" Then

Did you put the script in the sheet with your data like I explained?

This script only runs when you enter those values in column H
It will not loop through all previously entered data entered in column H

Try this on a empty sheet. Enter the values mentioned in column H and see what happens.

yes, I put this code in my Tracker tab which has the data that needs to be copied. When I go through my drop down options in column H and select, either "Closed" or "Purchased" all that happens, in my Closed Loans tab, creates a new column. Column H. My Closed Loans only has columns A- G if that makes a difference. I tried this code on a new worksheet as suggested and it works great. Perhaps because my "Closed" and "Purchased" text is being selected by a drop down box using data validation? Is the code not recognizing this?

 

[Nichole09]

I know it sounds odd, and the code works perfectly in a new sheet. But on this sheet, it just creates another column in H and titles it ... "Column1" The data on my tracker sheet and my Closed Loans sheet are in tables. Does this matter? Also, I have a Sub worksheet_beforedoubleclick(ByVal Target As range, cancel As Boolean) and Sub worksheet_Activate() in my Tracker sheet as well. Are these maybe interfering? I checked, and it still doesn't work if I type Closed or Purchased on H column vs selecting it from data validation drop down. I am thinking maybe data in tables act differently with this code? I have no idea. I will try it with my new sheet to test this.

 

[My Aswer Is This]

Excel worksheets have about 15,000 columns
But you said:

My Closed Loans only has columns A- G​

What happened to the other 15,000 columns?

Being in a validation box should work
But the value has to change. If the cell already has one of those two values and you just select the same value from the validation listbox there is no change.
Do you have any other code in your sheet?
If so show it to me.

 

[Nichole09]

okay, okay okay...... it was working..... but everything copied to the very bottom of my Closed Loans tables, not the top

 

[My Aswer Is This]

Now that I look back at my script you said only copy columns A-F and then H