This sounds very dangerous to me.

If you make a typing mistake, you may have just wiped out the wrong line on 5
different worksheets.

But if you want...

Select the Final worksheet, rightclick on its tab and choose view code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet
Dim FoundCell As Range

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If
If IsNumeric(Target.Value) = False Then Exit Sub
If Trim(Target.Value) = "" Then Exit Sub

For Each wks In Me.Parent.Worksheets
If wks.Name = Me.Name Then
'do nothing
Else
Do
With wks.Range("a:a")
Set FoundCell = .Find(what:=Target.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)
End With

If FoundCell Is Nothing Then
'done with that worksheet
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End If
Next wks

End Sub

I think I'd use the same kind of code, but instead of it firing when I made a
typing change, I'd assign that code to a button from the Forms toolbar (in Row 1
with row 1 frozen so that it's always visible) and use this:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim myCell As Range
Dim ActSheet As Worksheet
Dim resp As Long

Set ActSheet = ActiveSheet
With ActSheet
Set myCell = .Cells(ActiveCell.Row, "A")
End With

If IsNumeric(myCell.Value) = False Then Exit Sub
If Trim(myCell.Value) = "" Then Exit Sub

resp = MsgBox(Prompt:="Are you sure you want to clean up: " _
& myCell.Value & "?", Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

For Each wks In ActSheet.Parent.Worksheets
If wks.Name = ActSheet.Name Then
'do nothing
Else
Do
With wks.Range("a:a")
Set FoundCell = .Find(what:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)
End With

If FoundCell Is Nothing Then
'done with that worksheet
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End If
Next wks

End Sub




Duncan wrote:

Niek,

Many thanks for this but I am not sure that this would get what i want.
I have played around with the code but it appears to be a one time
usage simply to delete duplicate data.

Perhaps i rambled on a bit too much above when i tried to explain!

I need something that can look at a number i put in to the next cell on
a 'finalised' tab, compare it against the 5 other tabs in the worksheet
and then either delete the containing rows or mark one of the cells in
the row so that i can dis-count it when i tally up. it will be a list
of finished numbers on this tab which are actioned as they are put on
(through the barcode scanner)

I will paste where i am with it below, perhaps i have already started
to go wrong but i am borrowing code from a previous project and trying
to amend it, the main hurdle is getting it to look at the 5 other tabs.

see code below:

Private Sub submitint_Click()
Dim txt As String
Dim TitleText As String
'sets up error handler
On Error GoTo errorhandler
'disable screeen flickering
Application.ScreenUpdating = False

'ensuring all fields are filled in
If bcn.Value <= "" Then
GoTo problem
Else
'set up range to search
Range("A1:A65536").Select

'sets the find option to match the textbox to the range
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

'here is the problem so far, I need to put something to search all
tabs and then do something to the ones found

'these lines will put a yes next to the number to say that it has
been found and deleted/or marked done
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = ("Yes")
' clear the cell for next entry
bcn.Value = ""
MsgBox "Done", vbOKOnly, " Status "
Exit Sub

End If

'show message box if match not found
errorhandler:
MsgBox "Barcode not found! Please retry", vbOKOnly, "Status"
bcn.Value = ""
bcn.SetFocus
GoTo nd

problem:
MsgBox "Nothing to Submit, Re-Scan Barcode and try again",
vbRetryCancel

nd:
End Sub

--

Dave Peterson