VBA: Generate listing based on another worksheet in the same workb
 

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

Generate listing based on another worksheet in the same workb
 

Use sort (by name), autofilter and hide column D

"choo" wrote in message
...
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

VBA: Generate listing based on another worksheet in the sameworkb
 

Hi,

if you search for a VBA solution, it could be something like this
(haven't tested it though):

sub sort()
cells.clearcontents
cells(1,1).value = "desc"
cells(1,2).value = "type"
cells(1,3).value = "name"
row1=2
row2=2
do
if worksheets("Sheet1").cells(row1,2).value = "ws"
worksheets("Sheet2").cells(row2,1).value =
worksheets("sheet1").cells(row1,1).value
worksheets("Sheet2").cells(row2,2).value =
worksheets("sheet1").cells(row1,2).value
worksheets("Sheet2").cells(row2,3).value =
worksheets("sheet1").cells(row1,3).value
row2=row2+1
end if
row1=row1+1
loop until worksheets("Sheet1").cells(row1,1).value = ""
worksheets("Sheet2").columns("C:C").sort key1:=range("c2"),
order1:=xlascending, _
header:=xlyes, ordercustom:=1, matchcase:=false, _
orientation:=xltoptobottom, dataoption1:=xlsortnormal
end sub

Hope it helps,
Alex

choo schrieb:
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

VBA: Generate listing based on another worksheet in the same workb
 

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

VBA: Generate listing based on another worksheet in the same w
 

Thank you all for the valuable feedback. I appreciate it.

"Tom Ogilvy" wrote:

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