Searching through data by certain "headers"

John, does your data contain a header row?

BarrieBarrie Davidson

My Mistake, I meant the classification column...please read

Okay, that's my mistake, I meant I wanteded to sort by the column with the
classifications in it. THe general idea is to monitor the change in classification.
If a chnage occure(ie from class1 to class2) I want to copy the new classification,
and the data that resides in the row with it. This data will be copied to another
workbook. Does this make my example table above a little clearer? Thanks again for
the help!!
Sincerely,
John

Re: My Mistake, I meant the classification column...please read

John, try this code.

Sub Copy_Data()
' Written by Barrie Davidson
Dim valuetoCompare
Dim currentFile As String

currentFile = ActiveWorkbook.Name
Range("A1").Select
Do Until Selection.Value = ""
If Selection.Value <> valuetoCompare Then
valuetoCompare = Selection.Value
Selection.EntireRow.Copy
Windows("Book3").Activate 'You need to change this to the proper file name
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Activate
Windows(currentFile).Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Note that you need to change the line that reads

Windows("Book3").Activate

to the proper file name that you want to copy the data to.

Hope this helps you out.

Regards,
BarrieBarrie Davidson

Re: My Mistake, I meant the classification column...please read

Using a loop to do this will be very slow (particularly one that does all that selecting and activating!).
Try the following which makes use of AdvancedFilter :-

Sub Copy_Data()
Dim sh As String, rng As Range, destRng As Range
sh = ActiveSheet.Name
Set rng = Range(("A1"), Range("A65536").End(xlUp))
Set destRng = Workbooks("Book3").Sheets("Sheet1").Range("A1")
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range(sh & "!_FilterDatabase").Copy destRng
ActiveSheet.ShowAllData
End Sub

Correction ....

Correction :-

Sub Copy_Data()
Dim sh As String, rng As Range, destRng As Range
sh = ActiveSheet.Name
Set rng = Range(("A2"), Range("A65536").End(xlUp))
Set destRng = Workbooks("Custom Workbook2").Sheets("Sheet1").Range("A2")
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range(sh & "!_FilterDatabase").EntireRow.Copy destRng
ActiveSheet.ShowAllData
End Sub

Much better solution Josef, thanks (nt)

Another question!

Hey guys, Thanks for the help but I am still experienceing problems. Here is what I need
in a little more detail:
C D E F G H I
Classification Junk Length Junk Junk Junk GoodData
class1 0 10
class1 40 12
class2 40 32
class3 40 44

Here is what I need in steps:
a) Scan down Length Row until I get a value other than zero(ie 40)
b) In this row where I find the length 40 I want to record the class1
in column C and also GoodData in column I. No the info in the Junk columns
c) Now I will look in column C for the transitions in classification. Thus,
when class1 becomes class2 I will COPY class2 in that row and Good Data
in that row and copy to the new worksheet. On the next row when it goes to
class3 it shoudl copy class3 and the good data in colum I.

I'm sorry if this is confusing and I hope smeone can lend me a hand as time is tight!!
Thanks again.

Sincerely,
John

Re: Another question!

If this is too much maybe you can answer this:

When I find a change in classification, how do I copy only specific itemed in that row of data?
In my case it would be just column C and Column I. Thanks again.
John

I presume Anon's answer to your later posting does it ?