Excel2007 - vba Advanced Filter Doesn't Calculate

[AGilmour]

Hi ,

vba Advanced Filter 2007 does not work whereas 1997..2003 does via a button click

I have previously used excel 97..2003 to calculate an advanced filter via vba, however I have now upgraded to 2007 and even after reviewing programming books the filter will not work in 2007 and doesn't pick up data, however the same macro in 2003 will work? I have found no data to confirm why this is as all the programming books say it is possible. Does anyone have any ideas. I am happy to email the code to anyone who can verify what the issue or who I can contact to sort the issue. I have spent the Xmas hols working on this to no avail. See below:

Sub Picture1_Click()
Dim rngData As Range
Dim rngCriteria As Range
Dim rngExtract As Range

'Define Database, Criteria & Extract Ranges

Set rngData = Workbooks("AFltr.xlsm").Worksheets_("Trial").Range("Database")
Set rngCriteria = ThisWorkbook.Worksheets("Trial").Range_("Criteria")
Set rngExtract = ThisWorkbook.Worksheets("Trial").Range_("Extract")

'Extract data with Advanced Filter
rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopytoRange:=rngExtract, Unique:=False

End Sub

I would appreciate any ideas whatsoever. Much appreciated.

Trainee Excel Jedi
aka Andrew

 

[venkat1926]

I am confused. why those underscores

for e.g

Worksheets_("Trial")
why not just
Worksheets("Trial")

similarly remove other underscores. and try