Advanced Filter problems using VBA in XL2007

[rob.allchurch]

I used to think I was OK in VBA, but now I'm not so sure ...

I have a sheet within which I have a list linked to a MOSS list.
(The file was created in XL2003 and I'm using XL2007, so the
synchronization functionality is working just fine)

I have another sheet on which I wish to filter selected records from
the MOSS-linked list.

The following code is called from the 'receiving' sheet:
Sub Advanced_Filter_Due_To_Complete_Projects()
Sheets("Projects Due For Completion").Activate
Range("H2").Value = ">=" &
Format(Range("To_Complete_Date_1").Value,"dd/mm/yyyy")
Range("I2").Value = "<=" &
Format(Range("To_Complete_Date_2").Value,"dd/mm/yyyy")
ActiveSheet.Range(Range("B5").End(xlDown),
Range("B5").End(xlToRight).Offset(1, 0)).ClearContents
Range("'MOSS Projects In Progress'!
Projects_In_Progress").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("H1:I2"),
CopyToRange:=Range("B5:E5"), Unique:=False
End Sub

The 'destination' sheet is 'Projects Due For Completion'
The 'source' sheet is 'MOSS Projects In Progress'
The MOSS-linked list is named 'Projects_In_Progress'.

If I record a macro whilst performing the filter, obviously everything
works just fine. Running the code afterwards though produces no result
at all.
I've tried qualifying the ranges in a variety of different ways
(sheets(...).range(...), range(...) etc.), but no joy.
I've also changed the criteria from being calculated as a formula to
being created as a string value and entered (as in the code above). I
read on another thread that it may be something to do with string
manipulation of date fields by XL? (the format on the source sheet
list is 'dd/mm/yyyy' as in the code above)

I can only guess as to why its not working - maybe something to do
with the list/'name' in XL2007? I have tried creating a dynamic range
name for the list "MOSS_Projects_In_Progress" referring to the list
just in case that's it, but that doesn't work either.

Basically, I'm stumped and can't see the solution. No doubt its
something obvious and I'll kick myself once its pointed out to me.

Can anyone help shed any light on this please?

Thanks,

Rob

 

[Tom Hutchins]

This might the problem...

Using the advanced filter creates the range names Criteria, Extract, and
_FilterDatabase (which is hidden) in the workbook, to keep track of the
previous Advanced Filter range specifications. If not deleted, they will keep
the macro from successfully running the advanced filter again. You are
creating the ranges when you record yourself performing the filter.

Here is a macro I have used to delete these ranges. Call it before
re-running your advanced filter.

Sub DelRngNames()
On Error Resume Next
ActiveWorkbook.Names(ShtName$ & "!Criteria").Delete
ActiveWorkbook.Names(ShtName$ & "!Extract").Delete
ActiveWorkbook.Names(ShtName$ & "!_FilterDatabase").Delete
End Sub

Replace ShtName$ with the name of the worksheet where the advanced filter
will be applied.

Hope this helps,

Hutch

 

[Rob Allchurch]

This might the problem...

Using the advanced filter creates the range names Criteria, Extract, and
_FilterDatabase (which is hidden) in the workbook, to keep track of the
previous Advanced Filter range specifications. If not deleted, they will keep
the macro from successfully running the advanced filter again. You are
creating the ranges when you record yourself performing the filter.

Here is a macro I have used to delete these ranges. Call it before
re-running your advanced filter.

Sub DelRngNames()
On Error Resume Next
ActiveWorkbook.Names(ShtName$ & "!Criteria").Delete
ActiveWorkbook.Names(ShtName$ & "!Extract").Delete
ActiveWorkbook.Names(ShtName$ & "!_FilterDatabase").Delete
End Sub

Replace ShtName$ with the name of the worksheet where the advanced filter
will be applied.

Hope this helps,

Hutch

Thanks for the response.

Alas, your suggestion didn't work. I've removed all the 'criteria'
and 'extract' range names from the workbook manually, and run this
extra code also, but still no joy. I have another 5 or 6 advanced
filters that run just fine, but there are 2 (same code, just different
filter criteria) that don't work. They both have 'date' criteria
where the other filters (that all do work) are just simple 'text'
criteria (i.e. where 'colour' = 'red').

Hence one of my thoughts that it could be something to do with the
'date' comparison criteria - perhaps the string needs to be
constructed differently when running an advanced filter through VBA
compared to the normal XL interface.

I'll keep looking, but any other hints/tips etc. much appreciated

Rob