Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically copy data from large worksheet into individual workb | Excel Worksheet Functions | |||
create a workbook or worksheet listing totals from different workb | Excel Discussion (Misc queries) | |||
link to worksheet from several other worksheets in different workb | Excel Discussion (Misc queries) | |||
How do we Extract/Save a single worksheet out of a Mutlipage workb | Excel Discussion (Misc queries) | |||
Can one print a list of worksheet tab labels contained in a workb. | Excel Discussion (Misc queries) |