Moving Rows based on a value in a particular cell

KathCobb

New Member
Joined
Jul 10, 2013
Messages
2
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:p></o:p>

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:p></o:p>


Join Date<o:p></o:p>
<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="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> <v:path 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]-->
clip_image002.gif
<!--[endif]-->
Re: Move Rows To AnotherWorksheet Based On A Cell Value In That Row <o:p></o:p>
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:p></o:p>


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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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,
 
Upvote 0

Forum statistics

Threads
1,224,543
Messages
6,179,427
Members
452,914
Latest member
echoix

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