Formula Help

[cobbcity]

Hi,

I am NOT an expert at Excel but hope someone can help me. In the past,
this group has been wonderful. Let's see if I can explain.

I have a series of worksheets in the same file. Each worksheet has a
list of accounts with account number in one cell, name of account in
the new column, a note in the third column and sales in the forth
column.

Some of these account are no longer in business so, in the note column
I key "OOB" so I know that account is no longer in business. The last
worksheet in the file is called "OOB" on it's tab.

After I make these lists of accounts, I then have to copy and paste the
data from each row marked "OOB" to the "OOB" worksheet to get a total.
Seems like this is a waste of time.

Is there a way to tell Excel when "OOB" appears in that cell, the rest
of that row (or specific fields) should automatically copy to the next
available row on the "OOB" worksheet so the list of OOB accounts
populates itself automatically on it's own spreadsheet as I am working?

Is that too much to ask?

 

[L. Howard Kittle]

Perhaps a modification of this change event macro will suffice. Adjust the
Target.Column, Offset and Resize to suit your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
Dim myRange As Range
Set myRange = Target.Offset(0, -3).Resize(1, 2)

If Target.Value = "oob" Then
myRange.Copy
Sheets("OOB").Activate
Sheets("OOB").Range("A2000").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues
Sheets("Sheet1").Activate
Target.Value = ""
Target.Offset(0, -3).Resize(1, 2).ClearContents
End If

End Sub

HTH
Regards,
Howard