Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Could you tell me the AdvanceFilter where is error? | Excel Programming | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |