This is Tailor made for using advanced filter (data=filter=Advanced filter)

Sub GetSubset()
Dim v(1 To 3, 1 To 1) As String
Dim v1 As Variant
Dim rng1 As Range, rng2 As Range
Dim rng As Range
v(1, 1) = "type"
v(2, 1) = "=""=ws"""
v(3, 1) = "=""=pc"""
v1 = Array("desc", "type", "name")
Set rng1 = Worksheets("Sheet2") _
.Range("A1:C1")
rng1 = v1
With Worksheets("Sheet1")
'
' establish database
'
Set rng = Worksheets("Sheet1") _
.Range("A1").CurrentRegion
End With

' establish criteria Range

Set rng2 = rng(1).Offset(0, rng.Columns.Count + 3)
Set rng2 = rng2.Resize(3, 1)
rng2.Formula = v
rng2(2).Formula = rng2(2).Value
rng2(3).Formula = rng2(3).Value
'
' Apply Filter
'
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rng2, CopyToRange:=rng1, _
Unique:=False
End Sub

--
Regards,
Tom Ogilvy


"choo" wrote:

Hi,
I'm trying to automate some processes in Excel. Scenario as followed: I
have a list of equipment in Sheet1. I would like to create a button in
Sheet2 that, when clicked, would list me all the equipment for type = "ws"
and "pc" for example. Sheet2 would only list selected column "desc, type and
name" and sort by "name". List in Sheet1 will grow.

Sheet1:
A B C D
1 desc type name $
2 dell pc pc1 10
3 hp ws ws2 40
4 sun ws ws1 15
5 hp printer prt1 8

Sheet2:
A B C
1 desc type name
2 dell pc pc1
3 sun ws ws1
4 hp ws ws2

Anyone can help me? Any pointer?

Thank you,
choo