Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default How to copy rows that meet criteria to another sheet in Excel

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default How to copy rows that meet criteria to another sheet in Excel

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?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default How to copy rows that meet criteria to another sheet in Excel

Bruce

Sub Copy_To_Sheet()
Dim i As Integer
Dim wks As Worksheet
Dim iLastRow As Long
Set wks = ActiveSheet
Application.ScreenUpdating = False
iLastRow = wks.Cells(Rows.Count, 3).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, 3).Value = 1 Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
Application.ScreenUpdating = True
End Sub

You could call this from event code like BeforeClose or BeforeSave or
WorksheetActivate or Deactivate


Gord Dibben MS Excel MVP

On Thu, 2 Nov 2006 11:38:01 -0800, 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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default How to copy rows that meet criteria to another sheet in Excel

I think I did everything you said. I get an error that there is a missing
field or invalid name in my extract Name.

What sheet and what cells do you have highlighted when you select Adv Filter?

"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?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default How to copy rows that meet criteria to another sheet in Excel

oops...I sent the post before clearing the irrelevent stuff from the bottom
of my "template".

Sorry.

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

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?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default How to copy rows that meet criteria to another sheet in Excel

The column titles in the Extract range must match the column titles in the
Database range. You don't have to use all of the titles and they don't have
to be in the same order...but, they must still match.

This would trigger the error you mentioned:
If there is no "Column_Total" heading in the Database, then you wouldn't be
able to pull it into the Extract range.

Does that help?

***********
Regards,
Ron

XL2002, WinXP


"Bruce" wrote:

I think I did everything you said. I get an error that there is a missing
field or invalid name in my extract Name.

What sheet and what cells do you have highlighted when you select Adv Filter?

"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?

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default How to copy rows that meet criteria to another sheet in Excel

I double checked my column titles. They do match. Even though I don't need
them all, I was using them all and in order.

Before clicking DataFilterAdvance Filter, what sheet is your cursor and
what cells does it have highlighted? If I don't have 2 rows selected, it
barfs.

"Ron Coderre" wrote:

The column titles in the Extract range must match the column titles in the
Database range. You don't have to use all of the titles and they don't have
to be in the same order...but, they must still match.

This would trigger the error you mentioned:
If there is no "Column_Total" heading in the Database, then you wouldn't be
able to pull it into the Extract range.

Does that help?

***********
Regards,
Ron

XL2002, WinXP


"Bruce" wrote:

I think I did everything you said. I get an error that there is a missing
field or invalid name in my extract Name.

What sheet and what cells do you have highlighted when you select Adv Filter?

"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?

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 138
Default How to copy rows that meet criteria to another sheet in Excel

I wish I knew more about this part. I really don't even know where to begin
with this.

I know how to write formula that nest 7 deep, but can't touch this stuff.

"Gord Dibben" wrote:

Bruce

Sub Copy_To_Sheet()
Dim i As Integer
Dim wks As Worksheet
Dim iLastRow As Long
Set wks = ActiveSheet
Application.ScreenUpdating = False
iLastRow = wks.Cells(Rows.Count, 3).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, 3).Value = 1 Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
Application.ScreenUpdating = True
End Sub

You could call this from event code like BeforeClose or BeforeSave or
WorksheetActivate or Deactivate


Gord Dibben MS Excel MVP

On Thu, 2 Nov 2006 11:38:01 -0800, 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?



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default How to copy rows that meet criteria to another sheet in Excel

You can actually start anywhere on the destination (Extract) sheet...as long
as you have at least 2 cells selected, one above the other, and the top one
must contain a value. A good starting point is probably the Extract titles
and the cells directly below them. Excel may warn you that it can't figure
out what the table is, but you can just plow through that message and keep
going.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bruce" wrote:

I double checked my column titles. They do match. Even though I don't need
them all, I was using them all and in order.

Before clicking DataFilterAdvance Filter, what sheet is your cursor and
what cells does it have highlighted? If I don't have 2 rows selected, it
barfs.

"Ron Coderre" wrote:

The column titles in the Extract range must match the column titles in the
Database range. You don't have to use all of the titles and they don't have
to be in the same order...but, they must still match.

This would trigger the error you mentioned:
If there is no "Column_Total" heading in the Database, then you wouldn't be
able to pull it into the Extract range.

Does that help?

***********
Regards,
Ron

XL2002, WinXP


"Bruce" wrote:

I think I did everything you said. I get an error that there is a missing
field or invalid name in my extract Name.

What sheet and what cells do you have highlighted when you select Adv Filter?

"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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy rows of one sheet into mutiple sheets based on column value Wesley Breshears Excel Discussion (Misc queries) 0 October 18th 06 03:19 PM
Excel: have add'l rows entered in sheet 1 always show up in sheet Sooz in Grants Pass Excel Worksheet Functions 0 September 18th 06 01:33 AM
paste excel sheet excluding hidden rows Bernie Excel Discussion (Misc queries) 3 March 5th 06 03:17 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
How can we delete rows permanently from excel sheet Nehal Shah Excel Discussion (Misc queries) 1 August 1st 05 01:58 PM


All times are GMT +1. The time now is 07:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"