Selecting typical Data and copying in a new sheet in other format

[volkerpeter]

Dear Members , i am in a need of a code that selects some typical cells from an excel file and copy/paste it to another empty sheet in the same file . I am posting the sample file as attatchment.

Explaination:

I have to select values starting row by row . The vlues will be selected from the coulmns , I , M , T , AA , AG , AN , and AU.

Suppose i start from 3rd row and column I , its empty , i move ahead , across the row , the , i have to search only columns I , M , T , AA , AG , AN , and AU. The first value , i am gonna find in the column AN , i will copy and paste this cell in the new sheet in new sheet , the cell next to this cell in the new sheet will get the value in the top most cell of the coulmn AN .

Now i move to the 4th row , i look in the column I and i get the value MCV24596801001D , i copy and paste it to the new sheet in the next row , the value in the cell to right of it will get the value Ait0 , as it was , it is the value in the topmost cell of the column , now , i move to the nxe coulmns , if i find MCV24596801001D again , i copy the value of the topmost cell in the column and paste it in the sheet . I do this untill i get different value from the same row in the next colums. Next , i move to next row , and look those specified columns .
In my sheet 2 , the values will be put as shown , in column A , i will move to the next row , everytime i have a different value , and in the column B , the corresponding values of the topmost cell in the column will be placed .


I have just taken the first three different valued i encountered , but i have to do it the way for all the different values i encounter in the sheet .

In case of any Confusion , please ask me , i will answering quickly, as i have to do this job in the next 8 hrs . I am still learning VBA , so i need your support guys

I hope someone can help me . I need this macro very urgent , Please help

Kind Regards

Peter

[will1128]

This might help, but it'll search through any range.

Public sub find&copyCell()
Dim c As Range

For Each c In ActiveWorkbook.Sheets(1)
Select Case c.value
Case c.value = 'MCV24596801001D'

[will1128]

This psuedo code might help, but it'll search through any range.

Public sub find&copyCell()
Dim c As Range

For Each c In ActiveWorkbook.Sheets(1).UsedRange
Select Case c.value
Case c.value = 'MCV24596801001D'
ActiveCell.Select
Selection.Copy ActiveWorkbook.Sheets(2).Range(ActiveCell.Row,ActiveCell.Column)
Case.....
End Select
End For

[volkerpeter]

What should i do with the other cases ?

[volkerpeter]

thanks