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]

Well if you have things working for you that's great.

You originally said in your first post:

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

​

But in this last post your saying you want it pasted to the top of the table.


When I tried your new script it does not work for me it seems to be overwriting previously entered data.
But your using this 65000 type of way to find next row which may be causing me problems.
Never versions of Excel have about 1.5 million rows.

But if all is working for you that is what is most important.
Glad your able to modify scripts to work for your needs.

​

​