SELECTING AN ITEM FROM AUTO FILTER !

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



*** Sent via Developersdex http://www.developersdex.com ***
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top