Excel AdvanceFilter vba copyToRange only working for topmost Row

Question

I have a sheet "RM" in a workbook, and another sheet "ST" in the same workbook. "RM" has Table2 and "ST" has Table 3

I use the following code to copy from RM to another sheet "Andy" refName is a Criteria in "Andy" and topRowName is the string "Andy!B1:M1"

Sheets("RM").Range("Table2[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range(refName), CopyToRange:=Range(topRowName), Unique:= _
        False

So far so good, everything works Now I want to copy from "ST" also to "Andy" but paste it a few rows below "topRowName" so that it doesn't overwrite so I do an offset

Sheets("ST").Range("Table3[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range(refName), CopyToRange:=Range(topRowName).Offset(5), Unique:= _
        False

but it throws a 1004 Error "Extract Range has a missing or invalid field name"

I tried to hardcode the values in the Range like this

Sheets("RM").Range("Table2[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range(refName), CopyToRange:=Range(Andy!B5:M5), Unique:= _
        False

But it still doesn't work. Is it because there is a table in the paste location? i tried to resize the table so that it doesn't clash with the new paste but its not helping. Any help appreciated

Answer 1

Thanks all, I found the answer to the reason why this was happening. I needed to clear out contents of the place I'm pasting to. Apparently Excel doesn't like to copy contents to a range if there is something already present, particularly a ListObject like a table. I assumed that it would just write on top of it.