Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy rows of one sheet into mutiple sheets based on column value | Excel Discussion (Misc queries) | |||
Excel: have add'l rows entered in sheet 1 always show up in sheet | Excel Worksheet Functions | |||
paste excel sheet excluding hidden rows | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
How can we delete rows permanently from excel sheet | Excel Discussion (Misc queries) |