Do While Help required

[gaurav23]

Hi,

I have 5 sheets in a file viz. Data,Class,A,B,C,D,E.

I have written a macro to extract the class column from Data and remove the duplicates and list them in Class sheet.

Now i want to write a macro which will autofilter the data based on cell value and paste them to the respective sheet.

Here is the code i have written.

Dim a as integer
Dim b as variant

Sheets("Class").Select
Range("A2").Select
a = Range("A2").Row

DO while activecell <> empty

b = Range("A" & a).Value
Sheets("Data").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter

ActiveSheet.Range("A1:" & lcol & lrow).AutoFilter Field:=8, Criteria1:=""" & b & """

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("""& b & """).Select

ActiveSheet.Paste

Sheets("Class").Select

Range("A" & a).Select

ActiveCell.Offset(1, 0).Select

a = a + 1

Loop

But it is generating a run time error at Auto filter but the criteria i want is to select automatically based on the cell values.

Please help me with this.

Thanks & Regards,
Gaurav.