data records in a table extracting
 

i have a table with many records in it. the second sheet
is for records that have been removed from the table
because they are no longer active. lets just say that i
put an "X" in column J to denote the record needing to be
removed. i need a macro that can look for the "X" and
select column A:J for that row, pull it out of the table,
and paste it to the bottom of a similar table on sheet 2
that shows the old records. i need it on the bottom
because they are to be in chronological order.

data records in a table extracting
 

Spence,

Sub testit()
Dim i As Long, lngFirstRow As Long, lngLastRow As Long
Dim rng As Range, rngDest As Range

With Sheet1
lngFirstRow = .Cells(1, 10).End(xlDown).Row
lngLastRow = .Cells(Rows.Count, 10).End(xlUp).Row
For i = lngFirstRow To lngLastRow
If UCase(.Cells(i, 10)) = "X" Then
If rng Is Nothing Then
Set rng = Range(.Cells(i, 1), .Cells(i, 10))
Else
Set rng = Union(rng, Range(.Cells(i, 1), .Cells(i, 10)))
End If
End If
Next
End With

If Not rng Is Nothing Then
Set rngDest = Sheet2.Cells(Rows.Count, 1).End(xlUp)
If Not IsEmpty(rngDest) Then Set rngDest = rngDest.Offset(1, 0)
rng.Copy rngDest
rng.Delete xlUp
End If
End Sub

Rob


"spence" wrote in message
...
i have a table with many records in it. the second sheet
is for records that have been removed from the table
because they are no longer active. lets just say that i
put an "X" in column J to denote the record needing to be
removed. i need a macro that can look for the "X" and
select column A:J for that row, pull it out of the table,
and paste it to the bottom of a similar table on sheet 2
that shows the old records. i need it on the bottom
because they are to be in chronological order.