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