Hi McCrimon,

Assume that the unique transaction numbers start in A2 on each sheet.

The following deletes all rows on New whose transaction number is found in
column A on any of the Day1, Day2, Day3 or Day4 sheets.

The Day1 sheet is then renamed "ArchiveCopy" with an appended date.

The following name changes are effecte:

Day4=== Day3
Day3=== Day2
Day2===Day1

Finally, the New sheet is renamed Day4.

Test this on a *copy* of your workbook.

'===============================
Sub TestIt()

Dim Arr As Variant
Dim sh As Worksheet, sh2 As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim delRng As Range
Dim rCell As Range
Dim Lrow As Long
Dim lastRow As Long
Dim i As Long

Application.ScreenUpdating = False

Arr = Array("Day1", "Day2", "Day3", "Day4")

Set sh = ThisWorkbook.Sheets("New")

Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng1 = sh.Range("A2").Resize(Lrow - 1)

For Each rCell In Rng1.Cells
For i = LBound(Arr) To UBound(Arr)
Set sh2 = Sheets(Arr(i))
lastRow = sh2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng2 = sh2.Range("A1").Resize(lastRow)
If Not IsError _
(Application.Match(rCell.Value, Rng2, 0)) Then
If Not delRng Is Nothing Then
Set delRng = Union(rCell, delRng)
Else
Set delRng = rCell
End If
Exit For
End If
Next i
Next rCell

If Not delRng Is Nothing Then delRng.EntireRow.Delete

Sheets(Arr(0)).Name = "ArchiveCopy" & _
Format(Date, "yyyy-mm-dd")

For i = LBound(Arr) + 1 To UBound(Arr)
Sheets(Arr(i)).Name = Arr(i - 1)
Next i

Sheets("New").Name = Arr(UBound(Arr))

Application.ScreenUpdating = False

End Sub
'===============================

The bove code should be pated into a normal module in the worbook holding
the data sheets.


---
Regards,
Norman



"mccrimmon" wrote
in message ...

pretty please :)


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474