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

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 __
> 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

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

"Héctor Miguel" wrote:

> 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.