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]

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

That's because you have data already in column A
The script looks to the lastrow in column A with data.

If you wanted it in the top row we would have to insert rows each time.

 

[Nichole09]

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

yes, but your code works for the entire row, just copies the data to the bottom of my table in Closed Loans for some reason. For example:

Sheet("Tracker") has a table in it, columns A-J of which row 1 has the titles of the columns

Sheet("Closed Loans") has a table in it as well but only goes through A-G, 1st row containing the titles of the columns

I want to copy the row in my tracker sheet that contains Purchased or Closed in column H, and only copy columns A-F of that row, skip G and copy H and paste it in Closed Loans starting in the 2nd row (since its a table and the first row is the title of the columns) and if data is already in that row, move to the next row down. I hope this explains things better than my first post. Your code works just paste the data at the very bottom of my table. Whats odd, is my table in Closed Loans only covers rows 1-115, and when I select Purchased or Closed from my Tracker sheet, this copied row goes to row 116 on my Closed loans sheet, its as if the code wants to skip the table and go to the next clean cell if that makes sense. ..

 

[Nichole09]

mind you, all the rows in the closed loans sheet are empty except for row 1, which has the title of each column.

 

[My Aswer Is This]

I have tested my script maybe 10 times and it does not copy the entire row when I test it.
It copies Column A to F and the column H

And the data is copied to what i known as the lastrow
This is the last row with data in column A

So to begin with we need to see why your saying it copies the entire row which means all 15,000 cells in the row.

Entire row means all 15 thousand cells in that row

Tell me the name of the Table we are pasting into.


​

 

[My Aswer Is This]

mind you, all the rows in the closed loans sheet are empty except for row 1, which has the title of each column.

I'm not sure what to say.
First you said it was not working.
Then when you used it on two empty sheets you said it was working I thought.
Then you said it was working but pasting the entire row but at the bottom the sheet

So I'm not sure what to say.

I'm not use to copying data from one table and pasting into another table.

Hopefully someone else here at Mr. Excel will see this post and be able to help.
I will continue to monitor this thread and see what I can learn.

 

[Nichole09]

I am sorry for the confusion, after looking at my sheet again, the "entire row" isn't being pasted, I just noticed column H from the Tracker sheet is not be pasted In column G of the Closed Loans. I thought that if column G was being skipped or not copied from the code, than column H would take its place in the closed loans sheet. For example:

I thought it would (or would like it to) take the Tracker Sheet (table1):
column A Column B Column C Column D Column E Column F Column G Column H
3/23 1234567 John Smith Johnson Refinance 3/30 4/2 Purchased

and paste the data in the Closed Loans Sheet (Table 3) like this:

column A Column B Column C Column D Column E Column F Column G Column H
3/23 1234567 John Smith Johnson Refinance 3/30 Purchased


is it possible to not leave column G blank in the Closed Loans sheet, but rather have column H data from the Tracker sheet take its place?

 

[My Aswer Is This]

Well I just guessed and assumed you wanted it the way I did it.
You never said exactly where to paste it. I can fix that but we are still having other issues are we not?

If I fix that one problem does it fix everything ?

 

[My Aswer Is This]

If you have data in column A below the table then I need to know the "Table" name
It may be Table1 or Table2 or you may have given it some other name.
Because your saying paste this in last row off Table not last row of sheet.

 

[My Aswer Is This]

If you have a Table on Sheets("Closed Loans")
and the Table is name "Table1" then use this script:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-23-18 8:35 PM 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").ListObjects("Table1").ListRows.Count + 1
        ans = Target.Row
        
        Cells(ans, 1).Resize(, 6).Copy Sheets("Closed Loans").ListObjects("Table1").DataBodyRange(Lastrow, 1)
        Cells(ans, "H").Copy Sheets("Closed Loans").ListObjects("Table1").DataBodyRange(Lastrow, "G")
         Rows(ans).Delete
    End If
End If
End Sub

 

[Nichole09]

Sorry for the delay My Answer Is this.... you have helped me solve this issue I was having!!! I wanted this code to paste the specified data from the top of my table down, but this code only copied it from the end of the table down... so I modified this code slightly and it works perfectly!!!! Thank you for all your help on this one!!!

Private Sub Worksheet_Change(ByVal Target As range)
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").range("A65536").End(xlUp).End(xlUp).Row
    ans = Target.Row
    
    Cells(ans, 1).Resize(, 6).Copy Sheets("Closed Loans").Cells(Lastrow + 1, 1)
    Cells(ans, "H").Copy Sheets("Closed Loans").Cells(Lastrow + 1, "G")
    End If
    End If
End Sub