SELECTING AN ITEM FROM AUTO FILTER !

[jay dean]

Hi -

In all the sheets of my workbook, I have a list in column Range("B:B").
For each list, I have created an autofilter by manually doing "Data" >>
"Filter" >> "AutoFilter". This allows me to manually go through each
sheet to use the autofilter to select the item I want in each list.

I need a macro that will loop through the sheets and just select any
item I have already made visible using the autofilter.

All the macro will do is just select that visible item in every sheet.


Thanks
Jay

 

[jay dean]

Please ignore this post....I have used the "hidden" property to solve
the problem and it works neatly!

Thanks
Jay

 

[Per Jessen]

Hi Jay

This should do it:

Sub SelectFilteredItem()
Dim TargetCol As String
Dim StartSh As Worksheet

Set StartSh = ActiveSheet
Application.ScreenUpdating = False
TargetCol = "B"
For Each sh In ActiveWorkbook.Sheets
sh.Activate
sh.Range(TargetCol & sh.Rows.Count).End(xlUp).Select
Next
StartSh.Activate
Application.ScreenUpdating = True
End Sub

If this question is related to your previous post the macro below might be
what you want:

Sub ConcatenateFilteredItems()
Dim TargetCol As String
Dim MyString As String
Dim StartSh As Worksheet

Set StartSh = ActiveSheet
Application.ScreenUpdating = False
TargetCol = "B"
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Display" Then
sh.Activate
MyString = MyString & sh.Range(TargetCol & _
sh.Rows.Count).End(xlUp).Value
End If
Next
Sheets("Display").Range("D12") = MyString
StartSh.Activate
Application.ScreenUpdating = True
End Sub

Best regards,
Per