conditional look up

[armagan56]

I'm trying a slightly complex look up, I have worksheet A which lists stock trades

Stock Date Price Quantity Cost
X 1/12/10 5 2 10
Y 1/1/11 2 2 4
Z 13/1/11 3 3 9
Z 17/1/11 2 4 8

on another worksheet B I have defined sectors for each of these 3 companies (X, Y & Z)

X - Commodities
Y - Finance
Z - Industrial

what I want is a macro to look up on worksheet A all trades in January and copy the finance trades (looked up on worksheet B) on a worksheet called "finance", and industrial on the "industrial" worksheet.

What is the simplest way to do this?

 

[baitmaster]

I would use Autofilter to show only the items you want, then copy the visible cells to a new sheet. The sheet name is the same as your autofilter criteria

try something like this (untested):

dim cl as range
 
for each cl in range("autofilterValues") ' this is your list on sheet B
    with range("autofilterRange") ' name your range of data
        .autofilter Field:=2, Criteria1:=cl.value
        .SpecialCells(xlCellTypeVisible).copy destination:= sheets(cl.value) ' assuming this exists?
    end with
activesheet.showalldata ' resets autofilter table
next cl

 

[armagan56]

sorry I don't understand, you want me to run an advanced filter on worksheet B, and filter out all records for example Y financial?

then create a macro such as;

Sub sort_stocks()
Dim cl As Range
For Each cl In Range("B") ' this is your list on sheet B
With Range("StockRange") ' name your range of data
.AutoFilter Field:=2, Criteria1:=cl.Value
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(cl.Value) ' assuming this exists?
End With
ActiveSheet.ShowAllData ' resets autofilter table
Next cl

End Sub