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

 

[Fluff]

How about

Sub FilterShts()
   Dim i As Long
   
   For i = 36 To 44
      Sheets("Week" & i).Range("B3:B1483").AutoFilter 1, Sheets("Sheet1").Range("K16").Value
   Next i
End Sub

 

[Dannycash]

How about

Sub FilterShts()
   Dim i As Long
   
   For i = 36 To 44
      Sheets("Week" & i).Range("B3:B1483").AutoFilter 1, Sheets("Sheet1").Range("K16").Value
   Next i
End Sub

Perfect - Thank you!!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Dannycash]

Glad to help & thanks for the feedback

Hi Fluff,

I need you advice again if possible...

If a site in B5 entered the trading times for Week 36 into E5:R5 and wanted the same times to apply to the following 8 weeks (Christmas Period), they would put "Yes" in Column S5. Based on the "Yes" I would like to copy the trading times from E5:R5 into E5:R5 in the following 8 Sheets (Week37, Week38 up to Week44) automatically?

Can this be done without having to press a button to start the macro?

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 Target.Address(0, 0) = "S5" And Target.Value = "Yes" Then
      For i = 37 To 44
         Sheets("Week" & i).Range("E5:R5").Value = Sheets("Week36").Range("E5:R5").Value
      Next i
   End If
End Sub

This need to go in the Week36 sheet module

 

[Dannycash]

Awesome! Thank you!

How do I get it to repeat down from S5 to S1483 in Week36 and perform the same check?

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 44
         Sheets("Week" & i).Range("E" & Target.Row).Resize(, 14).Value = Sheets("Week36").Range("E" & Target.Row).Resize(, 14).Value
      Next i
   End If
End Sub

 

[Dannycash]

Legend!! This now does exactly as I need it to!

Last question I promise....if any of the trading times in E:R are changed after the criteria of Yes has been entered in column "S", the rest of the sheets don't update.

I have tried to use:

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 44
      
         Sheets("Week" & i).Range("E" & Target.Row).Resize(, 14).Value = Sheets("Week36").Range("E" & Target.Row).Resize(, 14).Value
         
      Next i
      
   End If
   
    
  End Sub
  
Sub Calculate_Range()

 ActiveSheet.Range("D5:D1483").RefreshAll
  
End Sub

But with no luck - Is this because the copy/paste process is reliant on the "Yes" criteria rather than the individual cell values themselves?

Would a RefreshAll process be the right thing to use?

Thanks,

Danny

 

[Fluff]

Easiest way is to use a formula

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 = "=Week36!E" & Target.Row
      Next i
   End If
End Sub