Transferring specific data

[Jennifer]

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.

 

[Tom Hutchins]

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

 

[Tom Hutchins]

I am a lousy typist. Her name is Debra Dalgleish.

 

[Simon Lloyd]

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)

 

[Jennifer]

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]

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]

The headings match. Yesterday was just examples. Any ideas on the issue I am
having ?

 

[Simon Lloyd]

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)

 

[Jennifer]

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.

 

[Tom Hutchins]

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