VBA - filter multiple sheets based on cell ref

[Scottyfras111]

VBA to filter multiple sheets based on a cell on another sheet

I've been doing some digging and have found a few answers but nothing that does specifically what I'm trying to do. I have an excel file with over 10 sheets and four of these sheets I need to apply a filter to based on a selection made on another sheet. I'm currently using the code below, but...

www.mrexcel.com

I am trying to use this to solve my problem but keep getting error - vba subscript out of range error 9

Please help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Target.Address = "$X$1" Then
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
With ws

If .AutoFilterMode Then .ShowAllData
.Range("A1:BA100000").CurrentRegion.AutoFilter Field:=1, Criteria1:=Target.Value

End With
Next ws
End If
End Sub

 

[Scottyfras111]

its ok sussed it