Removing Select from code

[schielrn]

Below is my code that changes my "cover" sheet based on a filter in the "detail" sheet. My "cover" sheet uses subtotals and has a combo box on there to select different regions. I would like to get rid of the .selects, but I can't figure out how to use With sheets("detail") to get rid of them.

Any help would be appreciated. Thanks.

Sub DropDown2_Change()
Application.ScreenUpdating = False
If (Range("C15").Value = 2) Then
Sheets("detail").Select
Selection.AutoFilter Field:=1, Criteria1:="North Central"
Sheets("cover").Select
ElseIf (Range("C15").Value = 1) Then
Sheets("detail").Select
Selection.AutoFilter Field:=1, Criteria1:="West"
Sheets("cover").Select
ElseIf (Range("C15").Value = 3) Then
Sheets("detail").Select
Selection.AutoFilter Field:=1, Criteria1:="South Central"
Sheets("cover").Select
ElseIf (Range("C15").Value = 4) Then
Sheets("detail").Select
Selection.AutoFilter Field:=1, Criteria1:="HDQ"
Sheets("cover").Select
ElseIf (Range("C15").Value = 5) Then
Sheets("detail").Select
Selection.AutoFilter Field:=1
Sheets("cover").Select
End If
Application.ScreenUpdating = True
End Sub

 

[Lewiy]

Try this:

Sub DropDown2_Change()
Application.ScreenUpdating = False
Select Case Range("C15").Value
    Case 2
        Sheets("detail").AutoFilter Field:=1, Criteria1:="North Central"
    Case 1
        Sheets("detail").AutoFilter Field:=1, Criteria1:="West"
    Case 3
        Sheets("detail").AutoFilter Field:=1, Criteria1:="South Central"
    Case 4
        Sheets("detail").AutoFilter Field:=1, Criteria1:="HDQ"
    Case 5
        Sheets("detail").AutoFilter Field:=1
End Select
Sheets("cover").Select
Application.ScreenUpdating = True
End Sub

 

[Jonmo1]

You don't have to use WITH. With is only useful for doing SEVERAL things on one sheet...your doing like 1 thing on several sheets....kinda the opposite...
using Select Case might be helpful here too.
try

Sub DropDown2_Change() 
Application.ScreenUpdating = False 
Select Case Sheets("cover").Range("C15").Value
Case 2
    Sheets("detail").AutoFilter Field:=1, Criteria1:="North Central" 
Case 1
    Sheets("detail")..AutoFilter Field:=1, Criteria1:="West" 
Case 3 
    Sheets("detail").AutoFilter Field:=1, Criteria1:="South Central" 
Case 4
    Sheets("detail").AutoFilter Field:=1, Criteria1:="HDQ" 
Case 5
    Sheets("detail").AutoFilter Field:=1 
Case Else ' do nothing
End Select
Application.ScreenUpdating = True 
End Sub

 

[Lewiy]

You don't have to use WITH. With is only useful for doing SEVERAL things on one sheet...your doing like 1 thing on several sheets....kinda the opposite...
using Select Case might be helpful here too.
try

Sub DropDown2_Change() 
Application.ScreenUpdating = False 
Select Case Sheets("cover").Range("C15").Value
Case 2
    Sheets("detail").AutoFilter Field:=1, Criteria1:="North Central" 
Case 1
    Sheets("detail")..AutoFilter Field:=1, Criteria1:="West" 
Case 3 
    Sheets("detail").AutoFilter Field:=1, Criteria1:="South Central" 
Case 4
    Sheets("detail").AutoFilter Field:=1, Criteria1:="HDQ" 
Case 5
    Sheets("detail").AutoFilter Field:=1 
Case Else ' do nothing
End Select
Application.ScreenUpdating = True 
End Sub

I'm absolutely amazed how close we got to the same answer on this!!

I was expecting someone to come up with a completely different solution!

 

[Norie]

Lewiy

Not quite completely different.

Sub DropDown2_Change()
Dim arrRegs

    Application.ScreenUpdating = False

    arrRegs = Array("West", "North Central", "South Central", "HDQ", "")
    Sheets("detail").AutoFilter Field:=1, Criteria1:=arrRegs(Range("C15").Value - 1)
    
    Sheets("cover").Select
    
    Application.ScreenUpdating = True

End Sub

Oh, and totally untested at the moment.

Kind of worried about that last filter but it'll take me some time to set up test data.

 

[schielrn]

For all 3 it does not like when there is Sheets("detail").Autofilter. Not sure why, but I had tried that also with no avail. It says Named Argument not found?

 

[Scott Huish]

I believe you have to specify a range for the AutoFilter.

Also Case Else 'do nothing
is not necessary because if it doesn't meet any of the conditions it won't do anything anway.

 

[schielrn]

Is that because the filter is in range A4:H4?

 

[schielrn]

Got it. Thanks all 3 solutions worked except Norie like you said it didn't like the last filter criteria.

Sheets("detail").Range("A4:H4").AutoFilter

 

[Norie]

schielrn

There's nothing in your code to indicate where C15 was located, so I think some of us were just taking bit of a shot in the dark, and jonmo hit the target.

HOTPEPPER

I think you can use AutoFilter without a range but it needs to have been turned on.

And to turn it on you need to apply it to a range.

So you do need a range.

Oh no, I've just gone and confused/contradicted myself.