AdvanceFilter Problem
 

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").AdvancedF ilter _
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").AdvancedF ilter _
Action:=xlFilterCopy, _
CopyToRange:=PWBTempData.Range("A1"), _
Unique:=True
Debug.Print PWBTempData.Range("A1")
Exit Sub
errTrap:
MsgBox Err.Number & vbCrLf & Err.Description
End Sub

AdvanceFilter Problem
 

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").AdvancedF ilter

(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").AdvancedF ilter _
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").AdvancedF ilter _
Action:=xlFilterCopy, _
CopyToRange:=PWBTempData.Range("A1"), _
Unique:=True
Debug.Print PWBTempData.Range("A1")
Exit Sub
errTrap:
MsgBox Err.Number & vbCrLf & Err.Description
End Sub