Using VBA code to Copy and Paste Row

[Juan5]

I need to figure out a code to copy and paste an entire row when a keyword is typed into a specific column.
Starting in the Worksheet named "April", I use columns A thru T. When column S says "Closed" I need the entire row from A - T to be moved from Worksheet "April" to the worksheet named "Closed".
I will need this to happen from Rows 3 to 775.

Hopefully this makes sense. I am brand new to using VBA and Macros. I have done some research and tried to create my own codes but it hasn't worked.
Any help would be greatly appreciated.


- John

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col span="17"></colgroup><tbody>
</tbody>

 

[vcoolio]

@ Juan:

You're welcome Juan. Thanks for the feed-back.

@Joe:-

Thank you Joe for the insights and taking the time to explain. Its always good to have various opinions and the reasoning behind those opinions. Its makes one assess things a little more in depth.
I hope that the OP has learned something valuable from this thread.

Cheerio,
vcoolio.

 

[Juan5]

Would either one of you be able to help me with this same code...except that I would like to only move columns A,B,C,D,E,G,H,I,J,K,L,M,N,O,R,S rather than moving the entire row.
It might not be completely necessary so if we can't do it no big deal...but thought it wouldn't hurt to try.
Please let me know if there is a way to adjust this code to exclude certain columns.
Thank you.


- John

 

[vcoolio]

Hello Juan,

In the case of the code that I suppliied, try the following (untested):-

Option Compare Text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet: Set ws = Sheets("Closed")

If Intersect(Target, Sh.Range("S3:S775")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Sh.Name = "Closed" Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

x = Target.Row

If Target.Value = "Closed" Then
Union(Range("A" & x & ":" & "E" & x), Range("G" & x & ":" & "O" & x), _
Range("R" & x & ":" & "S" & x)).Copy ws.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.