Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes
I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
It sounds like an advanced filter may be the best way to copy the data to a
new sheet. See Debra Dalgeish's excellent instructions (and even a video) at http://www.contextures.com/xladvfilter01.html#ExtractWs You could record yourself creating & applying the advanced filter, then tweak the code generated to prompt for the filter criteria, output sheet name, etc. If you get stuck, post the code and I will help you. Hope this helps, Hutch "Jennifer" wrote: I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
I am a lousy typist. Her name is Debra Dalgleish.
"Tom Hutchins" wrote: It sounds like an advanced filter may be the best way to copy the data to a new sheet. See Debra Dalgeish's excellent instructions (and even a video) at http://www.contextures.com/xladvfilter01.html#ExtractWs You could record yourself creating & applying the advanced filter, then tweak the code generated to prompt for the filter criteria, output sheet name, etc. If you get stuck, post the code and I will help you. Hope this helps, Hutch "Jennifer" wrote: I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
Hmmm, try this: Code: -------------------- Sub Move_data() Dim rng As Range, MyCell As Range Dim Mybook As String Dim wbNew As Workbook Set wbNew = Workbooks.Add() Mybook = ThisWorkbook.Name Workbooks(Mybook).Activate Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell.Value = "01/01/2008" And MyCell.Offset(0, 25).Value = "Yes" _ And MyCell.Offset(0, 78).Value = "Closed" Or MyCell.Offset(0, 78).Value = "Closed - No Action" Then MyCell.EntireRow.Copy Destination:=wbNew.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell wbNew.SaveAs Filename:="Your path and name" End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38552 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
Tom, I must be missing something, I am looking to obtain al records that are greater than 12/31/2006, that are Legal, and is a defect (yes) and Cause is Yes or No). Here is what I put in my criteria range Month Cause Defect Origin 12/01/2006 ="=Yes" ="=Yes" ="=Legal" ="=No" The "Cause" and "Defect" pull correctly onto my new location, however Month and Origin pulls in all data. that equal the Cause and Defect criteria above. Also do I need to list in my list all the column headings that are in the List Range? "Tom Hutchins" wrote: I am a lousy typist. Her name is Debra Dalgleish. "Tom Hutchins" wrote: It sounds like an advanced filter may be the best way to copy the data to a new sheet. See Debra Dalgeish's excellent instructions (and even a video) at http://www.contextures.com/xladvfilter01.html#ExtractWs You could record yourself creating & applying the advanced filter, then tweak the code generated to prompt for the filter criteria, output sheet name, etc. If you get stuck, post the code and I will help you. Hope this helps, Hutch "Jennifer" wrote: I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
Is the problem just that your criteria headings don't match your data column headings? In your first post, you said column B is named "Date", but it looks like you called it "Month" in your criteria headings. They have to be exactly the same for the filter to know which field you mean. Is :Origin" the same as "Type" from your first post? Hutch "Jennifer" wrote: Tom, I must be missing something, I am looking to obtain al records that are greater than 12/31/2006, that are Legal, and is a defect (yes) and Cause is Yes or No). Here is what I put in my criteria range Month Cause Defect Origin 12/01/2006 ="=Yes" ="=Yes" ="=Legal" ="=No" The "Cause" and "Defect" pull correctly onto my new location, however Month and Origin pulls in all data. that equal the Cause and Defect criteria above. Also do I need to list in my list all the column headings that are in the List Range? "Tom Hutchins" wrote: I am a lousy typist. Her name is Debra Dalgleish. "Tom Hutchins" wrote: It sounds like an advanced filter may be the best way to copy the data to a new sheet. See Debra Dalgeish's excellent instructions (and even a video) at http://www.contextures.com/xladvfilter01.html#ExtractWs You could record yourself creating & applying the advanced filter, then tweak the code generated to prompt for the filter criteria, output sheet name, etc. If you get stuck, post the code and I will help you. Hope this helps, Hutch "Jennifer" wrote: I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
The headings match. Yesterday was just examples. Any ideas on the issue I am having ? "Tom Hutchins" wrote: Is the problem just that your criteria headings don't match your data column headings? In your first post, you said column B is named "Date", but it looks like you called it "Month" in your criteria headings. They have to be exactly the same for the filter to know which field you mean. Is :Origin" the same as "Type" from your first post? Hutch "Jennifer" wrote: Tom, I must be missing something, I am looking to obtain al records that are greater than 12/31/2006, that are Legal, and is a defect (yes) and Cause is Yes or No). Here is what I put in my criteria range Month Cause Defect Origin 12/01/2006 ="=Yes" ="=Yes" ="=Legal" ="=No" The "Cause" and "Defect" pull correctly onto my new location, however Month and Origin pulls in all data. that equal the Cause and Defect criteria above. Also do I need to list in my list all the column headings that are in the List Range? "Tom Hutchins" wrote: I am a lousy typist. Her name is Debra Dalgleish. "Tom Hutchins" wrote: It sounds like an advanced filter may be the best way to copy the data to a new sheet. See Debra Dalgeish's excellent instructions (and even a video) at http://www.contextures.com/xladvfilter01.html#ExtractWs You could record yourself creating & applying the advanced filter, then tweak the code generated to prompt for the filter criteria, output sheet name, etc. If you get stuck, post the code and I will help you. Hope this helps, Hutch "Jennifer" wrote: I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
Jennifer, did this code not work for you? Sub Move_data() Dim rng As Range, MyCell As Range Dim Mybook As String Dim wbNew As Workbook Set wbNew = Workbooks.Add() Mybook = ThisWorkbook.Name Workbooks(Mybook).Activate Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell.Value = "01/01/2008" And MyCell.Offset(0, 25).Value = "Yes" _ And MyCell.Offset(0, 78).Value = "Closed" Or MyCell.Offset(0, 78).Value = "Closed - No Action" Then MyCell.EntireRow.Copy Destination:=wbNew.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell wbNew.SaveAs Filename:="Your path and name" End Sub -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38552 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
I am not extremely fluent in VBA, so if I go to use this for other worksheets I may struggle a bit at this point and time. I liked the advanced filter idea, but I am missing something per my prior posting. All criteria labels match the list so I amnot sure if I am missing something when I create the "and" or the "or" criteria. "Simon Lloyd" wrote: Jennifer, did this code not work for you? Sub Move_data() Dim rng As Range, MyCell As Range Dim Mybook As String Dim wbNew As Workbook Set wbNew = Workbooks.Add() Mybook = ThisWorkbook.Name Workbooks(Mybook).Activate Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell.Value = "01/01/2008" And MyCell.Offset(0, 25).Value = "Yes" _ And MyCell.Offset(0, 78).Value = "Closed" Or MyCell.Offset(0, 78).Value = "Closed - No Action" Then MyCell.EntireRow.Copy Destination:=wbNew.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell wbNew.SaveAs Filename:="Your path and name" End Sub -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38552 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring specific data
Since you are creating an OR condition with your Cause firld criteria, you
need to repeat the other criteria on each line: Month Cause Defect Origin 12/01/2006 Yes Yes Legal 12/01/2006 No Yes Legal Notice I just put Yes, No, and Legal. you only have to use the ="=Legal" format if you have records with different values all beginning with the word Legal and you only wanted records that contained exactly the word Legal (Not Legal Fees, Legal Remittances, etc.) To only copy certain columns, list the exact headings for those columns only on the destination sheet (with no empty columns in between). In the filter dialog, specify those headings as your "Copy to:" range. To copy all the columns, you don't have to put any headings on the destination sheet; just specify the cell where Excel should start putting the copied, filtered data. Hope this helps, Hutch "Jennifer" wrote: The headings match. Yesterday was just examples. Any ideas on the issue I am having ? "Tom Hutchins" wrote: Is the problem just that your criteria headings don't match your data column headings? In your first post, you said column B is named "Date", but it looks like you called it "Month" in your criteria headings. They have to be exactly the same for the filter to know which field you mean. Is :Origin" the same as "Type" from your first post? Hutch "Jennifer" wrote: Tom, I must be missing something, I am looking to obtain al records that are greater than 12/31/2006, that are Legal, and is a defect (yes) and Cause is Yes or No). Here is what I put in my criteria range Month Cause Defect Origin 12/01/2006 ="=Yes" ="=Yes" ="=Legal" ="=No" The "Cause" and "Defect" pull correctly onto my new location, however Month and Origin pulls in all data. that equal the Cause and Defect criteria above. Also do I need to list in my list all the column headings that are in the List Range? "Tom Hutchins" wrote: I am a lousy typist. Her name is Debra Dalgleish. "Tom Hutchins" wrote: It sounds like an advanced filter may be the best way to copy the data to a new sheet. See Debra Dalgeish's excellent instructions (and even a video) at http://www.contextures.com/xladvfilter01.html#ExtractWs You could record yourself creating & applying the advanced filter, then tweak the code generated to prompt for the filter criteria, output sheet name, etc. If you get stuck, post the code and I will help you. Hope this helps, Hutch "Jennifer" wrote: I have a large worksheet, approximately 240 columns and 4000 rows. Sometimes I need to create seperate workbook and add only specific columns where specific criteria is met in some of the columns. For example, if column B (column name "Date") = "01/01/2008" and column AA (column name "Defect') ="Yes" and column CB (column name "Type") = "Closed" and/or "Closed - No Action". I would like to pull those rows of data into a new workbook or worksheet and copy/paste from there. Is there some VBA code I can use. If someone could provide me with a basic code, I could add form there. I already have a module written with all column names identified. I am not real fluent at writing code but can update once provided, pretty new at VBA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transferring data | Excel Worksheet Functions | |||
transferring data | Excel Discussion (Misc queries) | |||
Transferring data | Excel Discussion (Misc queries) | |||
Transferring Data | Excel Worksheet Functions | |||
transferring data | Excel Programming |