Cut and Move Specific Row then Delete Duplicate row

[Papi]

The workbook has three worksheets called HR, Data and Past. I need to have the cursor on a row in the HR worksheet and be able to cut that row, move the entire row to the Past worksheet (last row if possible) and then delete the blank row from HR and then find the same row in the Data worksheet matching the company name (unique identifier) that was moved, delete that row and return to the HR worksheet with the cursor on the row just below the one deleted. The above will be done on a one as basis.

Thanks

 

[JLGWhiz]

The workbook has three worksheets called HR, Data and Past. I need to have the cursor on a row in the HR worksheet and be able to cut that row, move the entire row to the Past worksheet (last row if possible) and then delete the blank row from HR and then find the same row in the Data worksheet matching the company name (unique identifier) that was moved, delete that row and return to the HR worksheet with the cursor on the row just below the one deleted. The above will be done on a one as basis.

Thanks

I'm guessing that the company name is in column D, Right? For both Data and Past!

 

[Papi]

Yes, they are in the same columns. Thanks.

 

[JLGWhiz]

Yes, they are in the same columns. Thanks.

Well I used column C, If that is not the correct column, you will need to make the corrections where noted.

Sub cutnDel()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range
Set sh1 = Sheets("HR")
Set sh2 = Sheets("Past")
Set sh3 = Sheets("Data")
Selection.EntireRow.Cut sh2.Cells(Rows.Count, 1).End(xlUp)(2)
Set c = sh3.Range("[COLOR=#b22222]C:C[/COLOR]").Find(sh2.Cells(Rows.Count, [COLOR=#b22222]3[/COLOR]).End(xlUp).Value, LookIn:=xlValues) 'If not col C, make changes here.
    If Not c Is Nothing Then c.EntireRow.Delete
Selection.EntireRow.Delete
End Sub

 

[Papi]

Thanks JLGWhiz,

Thanks for your efforts. I am running this to test but it is not doing what I expected. I will reply back in a few minutes.

 

[Papi]

What the macro is doing is copying and pasting the row of the HR worksheet to the bottom of the same worksheet. What I need it to do is copy the entire row to the last known row of the third worksheet which is the Past worksheet and then delete the data in the second worksheet called Data when it finds the name in column C matching the item moved and then delete that row. Does this make sense? I just noticed that my sheets 2 and 3 are reversed to the way you have them.

 

[JLGWhiz]

What the macro is doing is copying and pasting the row of the HR worksheet to the bottom of the same worksheet. What I need it to do is copy the entire row to the last known row of the third worksheet which is the Past worksheet and then delete the data in the second worksheet called Data when it finds the name in column C matching the item moved and then delete that row. Does this make sense? I just noticed that my sheets 2 and 3 are reversed to the way you have them.

Don't know how those results happened, it worked as described when I tested it. However try this code without the use of the object variables. See if it does what you want.

Sub cutnDel2()
Selection.EntireRow.Cut Sheets("Past").Cells(Rows.Count, 1).End(xlUp)(2)
Set c = Sheets("Data").Range("C:C").Find(Sheets("Past").Cells(Rows.Count, 3).End(xlUp).Value, LookIn:=xlValues)
    If Not c Is Nothing Then c.EntireRow.Delete
Selection.EntireRow.Delete
End Sub

 

[Papi]

Hello JLGWhiz,

That is perfect! Thanks for all your time and effort. A great piece of work.

 

[Papi]

Don't know how those results happened, it worked as described when I tested it. However try this code without the use of the object variables. See if it does what you want.

Sub cutnDel2()
Selection.EntireRow.Cut Sheets("Past").Cells(Rows.Count, 1).End(xlUp)(2)
Set c = Sheets("Data").Range("C:C").Find(Sheets("Past").Cells(Rows.Count, 3).End(xlUp).Value, LookIn:=xlValues)
    If Not c Is Nothing Then c.EntireRow.Delete
Selection.EntireRow.Delete
End Sub

If I need to reverse the above by returning one selected row from "Past" (Selection.EntireRow.Cut) into two worksheets called "Data" and "Main" how would I go about that so the "Past" row is cut and pasted as noted with the cursor ending on Row 2 of the "Main" worksheet
The difference is that rather than delete the same row from "Data" that it pastes the same row in "Data" and "Main" between row 2 and 3. I'm not sure how to get the command ""Selection.EntireRow.Cut Sheets("Past").Cells(Rows.Count, 1).End(xlUp)(2)" to paste in two worksheets.