Auto Filter List

[jfcby]

Hi,

I have a worksheet the data is setup like so:

Building RM # Type Size
Building 001 105 4
Building 001 106 101 4
Building 001 108 5
Building 001 107 5
Building 002 201 G 6
Building 003 205 J 6
Building 003 209 B 8
Building 003 121 8
Building 004 124 5
Building 004 123 5
Building 004 1 A 4

When A2 Building is filtered I need to be able to select each criteria
one by one and copy the Building and the data from E1 to Worksheet 2
beginning with A2.

Can the macro below be modified to do the above?

<BEGIN Macro Code>

Sub AutoFilterList()
'
With Selection.AutoFilter
.Field = 1
For Each crt In .Criteria1
MsgBox crt
Next
End With

<END Macro Code>

Thnak you for your help,
jfcby

 

[Don Guillett]

From your description, I can't figure out what you want. Building A2??
If desired, send your wb to my address below along with a complete
description and before/after examples.

 

[jfcby]

Hi Don,

Before Example:
Worksheet 1
Col 1 Col 2 Col 3
Row1 Bldg Rm # Type
Row2 Bldg1 102 6
Row3 Bldg1 105 4
Row4 Bldg2 101 8
Row5 Bldg2 302 6
Row6 Bldg2 501 6
Row7 Bldg3 108 4
Row8 Bldg3 201 6
Row9 Bldg3 309 8
Row10 Bldg3 310 6

After Example:
New Worksheet
Col 1 Col 2
Row1 Bldg Total
Row2 Bldg1 2
Row3 Bldg2 3
Row4 Bldg3 3

Thank you for your help,
jfcby

 

[Don Guillett]

One way. Assumes that col E:F of the source sheet is available

Sub makeuniquelistandcount()
'make list
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), Unique:=True
'create formulas
lr = Cells(Rows.Count, "e").End(xlUp).Row
For i = 1 To lr
Cells(i, "f").FormulaR1C1 = "=COUNTIF(C[-5],RC[-1])"
Next i
'move to new sheet
Cells(1, "e").Resize(lr, 2).Cut
Sheets.Add
ActiveSheet.Paste

End Sub

 

[jfcby]

Don,

Thank you for your help!

How do you specify a header row in the Advance Filter. Because Row One
is the header row. When I run the macro from A2 in E2 Bldg 1 is listed
twice. How can the macro be modified so that the Advance Filter can
begin from A2 and not include Bldg 1 twice?

<BEGIN MACRO CODE>

Sub MakeUniqueListAndCount_1()
'Make Unique List & Count

lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("E2"), Unique:=True
'create formulas
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lr
Cells(i, "F").FormulaR1C1 = "=COUNTIF(C[-5],RC[-1])"
Next i
'move to new sheet
Cells(1, "E").Resize(lr, 2).Cut
Sheets.Add
ActiveSheet.Paste
End Sub

<END MACRO CODE>

Thank for your help,
jfcby

 

[Don Guillett]

Advance filter needs a header row so run it from a1 as I did and you will
get:
Bldg 1
Bldg1 2
Bldg2 3
Bldg3 4