I have a combobox in an Excel form (long story..forced to create this form in excel and use VBA..would rather use fox and com) tha tI need to use the advance filter function to create another range list as the list source for another combobox. Everything works fine except this code:
Private Sub cboRetention_Change()
'*' get_items Macro 'commented
'*' recorded 6/19/00 by Chuck Williams 'commented
'*' Range("AI1:AL232").AdvancedFilter Action:=xlFilterCopy,_ CriteriaRange:=Range("L25:L26"), CopyToRange:=Range("AT:AV"),_ Unique:=False 'commented
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

Set rng1 = Worksheets("transfer").Range("itembyreten")
Set rng2 = Worksheets("transfer").Range("criteria")
Set rng3 = Worksheets("transfer").Range("extract")

Excel.Range(rng1).AdvancedFilter xlFilterCopy, CriteriaRange:=rng2, CopyToRange:=rng3, Unique:=False
End Sub
The error is "runtime error 1004", "method 'Range' of object '_global' failed" and the whole string is highlighted. I think the error is the way i am calling Excel.object.object. I believe something else needs to be reference, but what????

Thanks in advance for anyones help