View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 2,118
Default How to copy rows that meet criteria to another sheet in Excel

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

--
Try something like this:
Try this:

From the Excel main menu:
<<<

Is that something you can work with?
Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Here's an approach to try:

Sample Assumptions:
Sheet1 contains your data in cells A1:D100 with the following column titles
A1: MyCol_1
B1: MyCol_2
C1: MyRefCol.....(This one contains the data you want to filter on)
D1: MyLastCol

Sheet2 is where you want the extracted data to be displayed

Using Sheet2 (contains your column headings from Sheet1):
A1: MyCol_1
B1: MyCol_2
C1: MyRefCol
D1: MyLastCol

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

I1: MyRefCol
I2: 1

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:$D$100

(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

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bruce" wrote:

In one worksheet, I have a set of about 15 columns. For those rows that have
a value of 1 in column 3, I want to copy those to another worksheet. Both
worksheets are in the same workbook.

My current # of rows is only 170. I want sheet #2 to automatically grow as
things are added to sheet 1.

How?