Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to copy numbers based on criterias
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to copy numbers based on criterias
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to copy numbers based on criterias
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to copy numbers based on criterias
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 wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summarizing up cells based on two different criterias | Excel Worksheet Functions | |||
How to add data from cells based on different criterias? | Excel Worksheet Functions | |||
sum based on 4 criterias | Excel Discussion (Misc queries) | |||
Insert values based on 2 criterias | Excel Discussion (Misc queries) | |||
Counting instances based on two criterias | Excel Worksheet Functions |