Move Row Based On Cell

[CurtR]

I am trying to Move ( Archive) a row of data in Excel, based on a Value ( Date) being in a cell.
I want the entire row removed from the sheet where it resides ( "Master&quot and placed in another sheet ("Archived&quot within the same workbook, if the cell in Column "EA" has a date in it.
The blank row should be deleted from ("MASTER&quot and Inserted into ("ARCHIVED&quot
What I have ,and I am trying I get an error stating an Object is required at the ***Starred* ** Line of code below. And I am not getting it ! Is there an Easier way of doing this ?

Application.ScreenUpdating = False
ReturnSheet = ActiveSheet.Name
Range("EA6&quot.Select ' Select Completed Date Column
Set AreaRange = ActiveCell.CurrentRegion
Set MyRange = Intersect(ActiveCell.EntireColumn, AreaRange)

' Define Area that Matches Select Cell Value
x = Date
For Each Cell In MyRange

If Cell.Value = x Then
If i = 0 Then
Set NewRange = Cell.EntireRow
Else
Set NewRange = Union(NewRange, Cell.EntireRow)
End If
i = i + 1
End If

Next

' Copy & Paste
Sheets("Archived&quot.Select
Range("A6&quot.Select

NewRange.Copy ' ****This is where it errors stating Object required ****
Selection.Insert Shift:=xlDown
Application.CutCopyMode = xlCut
Range("A6&quot.Select
NewRange.Delete Shift:=xlUp

Sheets(ReturnSheet).Select
Application.ScreenUpdating = True
Sheets("MASTER&quot.Select
Range("AE6&quot.Select
End Sub

 

[logius]

Move your

NewRange.Copy

two lines up and replace it with this:

NewRange.Copy(Sheets("Archived").Range("A6"))

I think that'll work for you. You shouldn't have to select the destination sheet or range outside of the copy function. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com