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.

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.

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.

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.

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.

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.

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