Suppose I have a worksheet with this data:

Student Grade
Jack A
Jill B
Joe C
Sue B

How do I make a macro to select only students with grade B and put
them in another worksheet that shows:

Student Grade
Jill B
Sue B

(This is of course just a simplified view of my problem).

I've tried to record a macro using autofilter, go to select visible
cells only, copy and paste, etc. but when I examined the code I found
that it contained specific cell ranges and therefore not really usable
outside the immediate workbook...

Here's an approach to try:

Assumptions:
Sheet1 contains your data in cells A1:B10
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: Student
B1: Grade

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: Grade
I2: B

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$B$10

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data<Filter<Advanced Filter
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
.....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: InsertModule

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

To test, change the value of I2 and run it again.
Is that something you can work with?
Does that help?
***********
Regards,
Ron

XL2002, WinXP


"johannes" wrote:

Suppose I have a worksheet with this data:

Student Grade
Jack A
Jill B
Joe C
Sue B

How do I make a macro to select only students with grade B and put
them in another worksheet that shows:

Student Grade
Jill B
Sue B

(This is of course just a simplified view of my problem).

I've tried to record a macro using autofilter, go to select visible
cells only, copy and paste, etc. but when I examined the code I found
that it contained specific cell ranges and therefore not really usable
outside the immediate workbook...

If you just want to copy the data to another sheet in the current
spreadsheet, try the following:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/1/2007 by ChristopherTri
'

'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="A"
Columns("A:B").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=2, Criteria1:="B"
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

The macro only copies the A's and B'c, but you can expand it for all grades.
Excel does a good job copying and pasting filtered columns of data. If you
want to record the macro rather than text edit it, just select columns A and
B after you filter on the letter grade and before you copy.

Regards...

ChristopherTri



"Ron Coderre" wrote:

Here's an approach to try:

Assumptions:
Sheet1 contains your data in cells A1:B10
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: Student
B1: Grade

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: Grade
I2: B

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$B$10

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data<Filter<Advanced Filter
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: InsertModule

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

To test, change the value of I2 and run it again.
Is that something you can work with?
Does that help?
***********
Regards,
Ron

XL2002, WinXP


"johannes" wrote:

Suppose I have a worksheet with this data:

Student Grade
Jack A
Jill B
Joe C
Sue B

How do I make a macro to select only students with grade B and put
them in another worksheet that shows:

Student Grade
Jill B
Sue B

(This is of course just a simplified view of my problem).

I've tried to record a macro using autofilter, go to select visible
cells only, copy and paste, etc. but when I examined the code I found
that it contained specific cell ranges and therefore not really usable
outside the immediate workbook...