CODEWITHSUNDEEP
excelvba
user753014
user753014

Reputation: 21

Microsoft Excel Runtime Error '1004'

The extract range has missing or illegal field name.

code follows...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 Then
'calculate criteria cell in case calculation mode is manual
  Worksheets("ProductList").Range("C3").Calculate
  Worksheets("SalesData").Range("Table_HESCO") _
    .AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("ProductList").Range("C2:C3"), _
      CopyToRange:=Range("B18:V18"), Unique:=False
End If
End Sub

Upvotes: 2

Views: 2310

Answers (1)

user688334
user688334

Reputation:

The CriteriaRange and CopyToRange headings need to match with the Table_HESCO headings

Eg 

Sub test1()

    Sheets("SalesData").Range("Table_HESCO").AdvancedFilter Action:=xlFilterCopy,    CriteriaRange:= _
        Sheets("ProductList").Range("C2:C3"),   CopyToRange:=Sheets("SalesData").Range("B18:D18"), Unique:=False

End Sub

takes the filtered data from named range 'Table_HESCO' and places into SalesData with field names in B18:D18 based on criteria on ProductList with field name(s) in C2

Sheets("SalesData").Range("Table_HESCO")

Sheets("ProductList").Range("C2:C3")

Sheets("SalesData").Range("B18:D18")

Upvotes: 1

Related Questions