SELECTING AN ITEM FROM AUTO FILTER !

  • Thread starter Thread starter jay dean
  • Start date Start date
J

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
 
Please ignore this post....I have used the "hidden" property to solve
the problem and it works neatly!

Thanks
Jay
 
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
 
Back
Top