macro to copy numbers based on criterias

[matthias]

Hello guys,

I'm looking for a macro to do the following:

in sheet1, I have column A which contains the row numbers.
I have a column B with the names of persons in it (a certain person can
have multiple lines). I want the macro to go and find all the
rownumbers (in column a) where the name of the person (column b) is
equal to the name given in e.g. cell a1(criteria1) and where column c
fullfils the criteria in cell a2(criteria2). Then all the rownumbers
have to be copied to sheet2 in column A. If the above criteria or not
fullfilled then the rownumber may not be copied to sheet2.


Is this possible ?

Thanks

 

[paul.robinson]

Hi
you could try Advanced filter. You get the option of filtering to sheet
2 (the copy bit). The Excel Help will take you through it.
regards
Paul

 

[matthias]

Yes but the problem is that I want to do that automatically, so a macro
is needed. Maybe I can use this filter in the macro...

 

[paul.robinson]

Hi
you can record the advanced filter:
Suppose your data is like this on sheet1

Row Name Number
1 John 3
2 Paul 2
3 John 3
4 John 2

and these five rows occupy A4:C8

Suppose your criteria look like this

Name Number
John 3

and these two rows occupy A1:B2

Record the following steps:
Activate Sheet2
Run advanced filter where ListRange is the five rows of data (select
using mouse), Criteria Range is the two rows of Criteria (select using
mouse) and Copy To is cell A1 (select using mouse).
Stop the record.
You will get

Sub Macro1()
Sheets("Sheet2").Activate
Sheets("Sheet1").Range("A4:C8").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("A1:B2"),
CopyToRange:=Range("A1"), _
Unique:=False
End Sub

regards
Paul