Updating AdvancedFilter in VBA does not work

[DaiCar]

Dear Excel-Masters,

As a newbie to this Forum I have a request I have been working on some hours now I have been charged with building an Excel-based customer database. Therefore, I wanted to build an Advanced Filter in VBA to copy the data from the database to a new sheet

So far the following code has worked once and the other solutions to this problem have not been fruitful. There must be a mistake somewhere but I can't find it...

Table1 ("List") = target sheet
Table2 ("Core_Data") = source sheet
Also I have created a range name called "Core_Data" which should adjust dynamically to the whole sheet:

=Core_Data!$A$2:INDEX(Core_Data!$A:$P;COUNTA(Core_Data!$A:$A);COUNTA(Core_Data!$1:$1))

Module Sub:

Sub FilterMeAdvanced()
Calculate
Tabelle1.Range("A8").Select
Range("Core_Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("W1:AB2"), CopyToRange:=Range("A8:P8"), Unique:=False
End Sub

Worksheet-Function (supposed to refresh after changing the filter criterion):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Worksheet.Name = "List" And Target.Column = 1 And Target.Row = 4 _
Or Target.Column = 2 And Target.Row = 4 _
Or Target.Column = 3 And Target.Row = 4 _
Or Target.Column = 4 And Target.Row = 4 _
Or Target.Column = 5 And Target.Row = 4 _
Or Target.Column = 6 And Target.Row = 4 _
Or Target.Column = 7 And Target.Row = 4 _
Or Target.Column = 8 And Target.Row = 4 _
Or Target.Column = 9 And Target.Row = 4 _
Or Target.Column = 10 And Target.Row = 4 _
Or Target.Column = 11 And Target.Row = 4 _
Or Target.Column = 12 And Target.Row = 4 _
Then
Application.SendKeys ("%{down}")
End If
End Sub

So far the filter worked once (by using the Sub) and hasn't changed ever since

Many thanks in advance for your help

 

[DaiCar]

I have realized that this dynamic range name is problematic - does somebody know how create a dynamic range name list? This code seems to be wrong:

=Core_Data!$A$2:INDEX(Core_Data!$A:$P;COUNTA(Core_Data!$A:$A);COUNTA(Core_Data!$1:$1))