Transferring data from one worksheet to another based on criteria

[dread]

I have a workbook with numerous worksheets. Each worksheet represents a
category (ie: cabinets, countertops, appliances). In each worksheet I have
a listing of options. Options are selected by entering a Yes in the select
column.

Here’s what it looks like:
Category: Option: Select:
Worksheet 1
Cabinets Oak Yes
Cabinets Cherry No
Cabinets Mahogony Yes
Worksheet 2
Countertops Brown Granite No
Countertops Green Granite Yes
Countertops Marble No
Countertops Laminate Yes

I want to transfer all the Yes options from each of the Category worksheets
(Worksheet 1 and Worksheet 2) to another worksheet (Worksheet 3). How can I
do this so that Worksheet 3 doesn’t contain blank lines(the No selections)
between each category?

Thanks.

 

[PancakeBatter]

Hi Dread,

You can use Data, Filter, Auto Filter. Then you can copy what you have
selected- it will select only those in the active filter.

Does that help?

PB

 

[dread]

Hi PancakeBatter,

I tried the filter and it works but how can I set this up so it's automated
(for instance a user clicks on a cell labeled "Run filter" and the filter is
run and the results are copied to another sheet)?

Thanks,

Dread

 

[PancakeBatter]

Ah. Gotcha. Well, here is somethting I use to capture a value for a column
I want to filter.

Sub Query_Wood

'Query_Wood Macro

Workbooks("Book.xls").Activate
Call Raw
Cells.Select
Selection.EntireColumn.Hidden = False
Tag = InputBox("Input WoodType:", "WoodType", WoodType)
Columns("A:A").Select
Selection.AutoFilter Field:=1, Criteria1:=WoodType.
EndSub

Then to select the filtered data and create another sheet I just created
this macro (Tools, Macro, Record Macro):

Sub Test

'test Macro

Rows("1:1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Data").Select
Sheets.Add
ActiveSheet.Paste
End Sub

Does that help?

PB

 

[dread]

Unfortuantely I don't understand any of your code and what it does. Would I
replace "Book.xls" with my workbook's name? And what would I replace "Input
WoodType", "WoodType",WoodType with? And does Sheets("Data").Select need to
be changed to something? Are these both Macros?

Thanks,
Dread

 

[PancakeBatter]

Unfortuantely I don't understand any of your code and what it does.
Here is a starting point maybe:

http://office.microsoft.com/en-us/assistance/HP052047111033.aspx

Would I replace "Book.xls" with my workbook's name?
Yes.

And what would I replace "Input > WoodType", "WoodType",WoodType with?
The items in quotes are text that will appear in a prompt. WoodType is a
variable that will store the users response. So if you have multiple types
of wood- pine, maple, oak and the users types in "oak", it will data select
on the value "oak"

And does Sheets("Data").Select need to be changed to something?
That was the name of the worksheet I right clicked then selected Insert to
insert a new worksheet. "Data" is found on the worksheet tab. Default names
for Worksheets are "Worksheet1", "Worksheet2", etc... but I changed the name
of mine.

Are these both Macros?
Yes.

PB