Copying row from one worksheet to another, then deleting that row

Nichole09

Board Regular
Joined
Aug 27, 2016
Messages
132
Hello,

I need some help with the below code. I know its pretty messy, but hopefully someone can help me clean it up and get it working. The first portion works. The code is assigned to a checkbox. If the checkbox is ticked my activesheet (Status of Closing) range D5 value changes to "Closed" and it will update my Tracker Sheet for that particular loan number and update that status to Closed as well so the two statuses match. (my loan number on my "Status of Closing" sheet is located in cell D4 and a matching loan number will be on my "Tracker" Sheet in column A, however the row varies for each loan number as there is a running list of several that other users can enter)

Second part, is not working. If the status of this loan changes to "Closed" I need my code to find the matching loan number on sheet "Tracker" (which is found in the first section as it updates the status for that particular loan.) and take the row and paste it to the "Closed Loans" Sheet and delete that row from the Tracker Sheet were it was originally copied from. I appreciate any help on this! Thank you!

Code:
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Sub Loan_Closed()
Dim ws As Worksheet
Dim xlrange As Range, xlrange1 As Range, Rng As Range
Dim valuetofind As String, valuetofind1 As String
Dim ans As Long
Dim Lastrow As Long
Dim answer As String
Dim MyNote As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set ws = Sheets("Status of Closing")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Application.EnableEvents = False
If ws.CheckBoxes("Check Box 406").Value = 1 Then
ws.Unprotect Password:="GoTeam!"
ws.Range("Y57").Value = Date
ws.Range("D5").Value = "Closed"
ws.Protect Password:= "[LEFT][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"></strike>
valuetofind = ws.Range("D3").Value
Set xlrange = Worksheets("Tracker").Range("A2:A200")[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]For Each cell In xlrange
If cell.Value = valuetofind Then
cell.Offset(0, 6).Value = ws.Range("D5").Value
cell.Offset(0, 7).Value = ws.Range("Y57").Value
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]Next[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]ws.Protect Password:=[LEFT][COLOR=#222222][FONT=Verdana] "[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT]
Else
ws.Unprotect Password:=[LEFT][COLOR=#222222][FONT=Verdana] "[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT]
ws.Range("R57") = ""
ws.Protect Password:=[LEFT][COLOR=#222222][FONT=Verdana] "[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]GoTeam!"[/FONT][/COLOR][/LEFT]
End If
ActiveWorkbook.save[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]MyNote = "Would you like to move this loan to the Closed Loans tab?"[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]If Worksheets("Status of Closing").Range("D5").Value = "Closed" Then
answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Closed Loan")[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]If answer = vbYes Then
Lastrow = Sheets("Closed Loans").Range("A65536").End(xlUp).End(xlUp).Row
Lastrow1 = Sheets("Tracker").Range("A65536").End(xlUp).End(xlUp).Row
End If
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]For Each Rng In Sheets("Tracker").Range("A2:A" & Lastrow1)
If Rng = Worksheets("Status of Closing").Range("D5").Value Then
Rng.EntireRow.Copy Sheets("Closed Loans").Cells(Lastrow + 1, 1)
Rng.EntireRow.Delete
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]Next[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]If answer = vbNo Then
End If[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]Exit Sub[/I][/B][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][B][I]
Application.EnableEvents = True
End Sub

[/I][/B][/FONT][/COLOR][/LEFT]
[B][I][LEFT][COLOR=#222222][FONT=Verdana]
[/I][/B]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top