On May 7, 5:12 pm, Tom Hutchins
<TomHutch...@discussions.microsoft.com> wrote:
> 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.allchu...@googlemail.com" wrote:
> > 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
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
|
Similar Threads
