AdvanceFilter Problem

[Lionel H]

I keep stuff (e.g. rarely changing data, partial results etc.) in worksheets
in my Personal WorkBook. I don't have to, but it keeps things neat.

In the code below, the sheets "CurrentDetails" and "OtherSheet" are in a
workbook called Progress_Review.xls, "CurrentDetails" contains data
extracted from a SQL Server DB using Query Analyser with field names in Row
1. The Field Name for Column C is "Assigned Team"
The problem, set out in the code below, is, I hope, self explanatory.
Can anyone please explain why the code I want to use fails?

Sub Debug_AdvancedFilter()
On Error GoTo errTrap
'This example works
ActiveSheet.Range("C1:C335").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("U1"), _
Unique:=True
'This example also works even if "CurrentDetail" is not Activesheet and
'even though the GUI for the AdvanceFilter method insists that
'"You can only copy filtered data to the active sheet."
Sheets("CurrentDetail").Range("C1:C335").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("OtherSheet").Range("A1"), _
Unique:=True
'These two lines lets me know PWBTempData is correctly SET (albeit
elsewhere)
PWBTempData.Range("A1").Value = "TestData"
Debug.Print PWBTempData.Range("A1")
'This example (the one I want to use) fails with
'Run-Time Error 1004 - The extract range has a missing or illegal field
name
Sheets("CurrentDetail").Range("C1:C335").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=PWBTempData.Range("A1"), _
Unique:=True
Debug.Print PWBTempData.Range("A1")
Exit Sub
errTrap:
MsgBox Err.Number & vbCrLf & Err.Description
End Sub

 

[Héctor Miguel]

hi, Lionel !

the line you want to use and (actually) fails with the message:
-> "Run-Time Error 1004 - The extract range has a missing or illegal field name"
is (probably) due to in A1 -> line: CopyToRange:=PWBTempData.Range("A1")
has different title than C1 -> line: Sheets("CurrentDetail").Range("C1:C335").AdvancedFilter

(if previously) you "set" the title in line: -> PWBTempData.Range("A1").Value = "TestData"

hth,
hector.

__ OP __

 

[Lionel H]

Hi Hector,
Thanks very much, that's sorted it.

For anyone else looking in:
In fact testing shows that the code also works if the first cell in the
destination range is empty. It only fails if the first cell in the
destination range is not empty and is different from the fieldname of the
range being filtered.

Once again thanks,
Lionel