Set data filter based on user selections

[jday]

I have a file that contains two user-defined lists. When the user sets
values for these lists & clicks a macro button, I want the macro to go to the
master data tab and filter the appropriate columns based on these values.
(From there, I have additional code that does something else with the
filtered data).

I am unsure how to construct the code that filters multiple items from a
list. Can someone lend some guidance on how to do this? (One other thing
in case it matters---the user list may contain anywhere from 1-100 items)

 

[Joel]

See VBA help "AdvancedFilter Method". You need to put your filter list on a
range in the workbook and set the CriteriaRange in the Advance help method.

 

[Bernie Deitrick]

jday,

Assuming your list is in cell A2, and the other sheet is named Master Data:

Sub FilterOtherSheet()
Dim myKey As Variant
Dim myArea As Range
Dim myCol As Integer

myCol = 2 'Column B
myKey = Range("A2").Text
Set myArea = Worksheets("Master Data").Cells(1, myCol).CurrentRegion
myArea.AutoFilter Field:=myCol - myArea.Column + 1, Criteria1:=myKey
End Sub

HTH,
Bernie
MS Excel MVP

 

[jday]

Worked like a charm! Thank you so much Bernie.

jday,

Assuming your list is in cell A2, and the other sheet is named Master Data:

Sub FilterOtherSheet()
Dim myKey As Variant
Dim myArea As Range
Dim myCol As Integer

myCol = 2 'Column B
myKey = Range("A2").Text
Set myArea = Worksheets("Master Data").Cells(1, myCol).CurrentRegion
myArea.AutoFilter Field:=myCol - myArea.Column + 1, Criteria1:=myKey
End Sub

HTH,
Bernie
MS Excel MVP