Moving Rows based on a value in a particular cell

[KathCobb]

I recently came across the thread I have copied and pasted below and am wondering how to adapt this to meet my needs. I, too, am very new to Ecel macros...so new that I am not 100% sure how to create one but I am sure I could google that. I have, thou, worked with VBA in Access a little bit, but not to the extent of the code below. I have a spreadsheet that keep track of statements paid dates of commissions. My paid date is also column J and I manually enter a date. What I would like to happen is that whenever a date is manually entered, that the entire row is moved from the "Client List" worksheet to the "paid" worksheet. All in the same work book. The thread I found from last year is as follows: <!--[if !supportLists]-->1. <!--[endif]-->Move Rows To AnotherWorksheet Based On A Cell Value In That Row <o></o>

I am very very new to macros, actuallytrying to teach myself with Excel 2010.

I currently have a Weekly Status report which contains data regardingcategory..description..yada yada. I want to move the entire row to anothersheet (within the same workbook titled "completed"), if the"Working Status" (column J) is equal to "completed".

Is there a code I can use to automate this function so I can avoid copy andpasting as I go?

-Thanks!
Kris <o></o>

---

​

Join Date<o></o>
<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/></v:shapetype><v:shape id="Picture_x0020_14" o:spid="_x0000_i1026" type="#_x0000_t75" alt="Default" style='width:12pt;height:12pt;visibility:visible; mso-wrap-style:square'> <v:imagedata src="file:///C:\Users\KATHC_~1\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png" o:title="Default"/></v:shape><![endif]--><!--[if !vml]-->

<!--[endif]-->Re: Move Rows To AnotherWorksheet Based On A Cell Value In That Row <o></o>
Welcome to the Board!

Give this a shot:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
Dim i As Long
' Set Target Range, i.e.Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("J:J")
' Only look at single cellchanges
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) aremet (do your thing here...)
If LCase(Target.Value) = "completed" Then
i= Target.Row
Target.EntireRow.CutSheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Cells(i,"J").EntireRow.Delete
End If
End Sub

HTH, Smitty<o></o>

My question is about the Range. I want the range to be unlimited. The spreadsheet will go on forever and I will not know how many will remain unpaid. I also want to make sure that this is continual. I need to paste the rows into the "paid" sheet each time it updates. It can be immediate, but probably on close, or on run by my choice maybe? So I also want to make sure it starts at the next available row on the paste sheet. I don't want anything overridden. And finally I recognize on the LCase statement that the case is "completed", I would need mine to be a date. Is there anyone out there that could help me make this work for my situation? I would really really appreciate the help.

 

[Smitty]

Welcome to the Board!

The code in your example will work on each row in column J. As for the date entry, if you don't need to validate that it's a proper date or meets some other criteria, then you can just get rid of the If statement entirely. And as it stands now, it will paste the cut row to the next empty row in Sheet2.

HTH,