Filtering Accross Multiple Worksheets from Drop Down List

[Dannycash]

Hi all, first post on here.</SPAN>

I’m having trouble cracking the VBA code I need to allow a user to select a region name from a drop-down list on Sheet1 that will filter results (based on their selection) in 9 other named worksheets (Week36 through to Week44).
</SPAN>
The drop-down list is sourcing the list of region names from another worksheet within the book.</SPAN>

Each WeekXX worksheet is laid out exactly the same, the region name is located in column B in each worksheet with the header name located in B3.</SPAN>

I’m not an excel rookie but haven’t ever really explored the world of VBA. Many of the other posts I have read don’t seem to work when selecting an option/name from a list.</SPAN>

Any help would be greatly appreciated. </SPAN>

I am using Excel 2016 if that helps?</SPAN>

This is as far as I got: (Beds & Herts is one of the region names)

Sub Macro2()
'
' Macro2 Macro
'
'
    Range("K16").Select
    Sheets("Week 36").Select
    ActiveSheet.Range("$B$3:$B$1483").AutoFilter Field:=1, Criteria1:= _
        "Beds & Herts"
    Sheets("Week 37").Select
    ActiveSheet.Range("$B$3:$B$1483").AutoFilter Field:=1, Criteria1:= _
        "Beds & Herts"
    Sheets("Week 38").Select
    ActiveSheet.Range("$B$3:$B$1483").AutoFilter Field:=1
End Sub

 

[Dannycash]

Thanks bud,

How would I get it to skip blank cells in E:R so it doesn't pull through 00:00:00 ?

Thanks,

Danny

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i As Long

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("S5:S1483")) Is Nothing And Target.Value = "Yes" Then
      For i = 37 To 38
         Sheets("Week" & i).Range("E" & Target.Row).Resize(, 14).Formula = "=if(Week36!E" & Target.Row & "="""","""",Week36!E" & Target.Row & ")"
      Next i
   End If
End Sub