EXCEL Advanced Filter - copying to another sheet

[QCLady]

Thread68-869424 was archived - still need help copying filtered data to another work sheet within the same Workbook.

I finally figured out how to apply the filter I needed and the following from Geoff, xlbo, 29 Jun 04 - seem to address 'why' I couldn't make it work.

"You CAN copy to another sheet - it's a bit of a bug but can be done . . . start the Adv. filter from the sheet you want to export to . . . etc."

I did that, identified the dataset sheet and data range and criteria range but, when I applied filter while in "target sheet" rec'd "Database or list range not valid". I doublechecked range selection (from the dataset sheet) but I must be missing something.

I also checked and followed each step on

http://www.contextures.com/xladyfilter01.html

Working in Office EXCEL 2003 SP3

I just want to copy rows meeting certain criteria to a separate sheet (Header = Error? Y or N . . . I just want the 'Y' rows). Seems simple but I can't get it! Sure would appreciate some help.

QCLady

 

[GlennUK]

Are you doing this within a macro?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[QCLady]

No . . . I just used the Data > Filter > Advanced Filter options from the tool bar.

I am inexperienced re Macros. Is that the better way to go?

Thanks for responding!

 

[GlennUK]

No, macros aren't necessarily a better way to go.

Your message "Database or list range not valid" seems to be indicating that there is something strange about the list range you are trying to filter. Can you tell us all what the extent of the range is ( how many columns, how many rows ), what the titles are, and how you specified the List range exactly in the Advanced Filter dialog?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Gavona]

I would strongly recommend using named ranges when filtering between sheets. I can't recall if you have to do it this way around (you may not when using named ranges) but I always run the advanced filter from the destination sheet as Geoff advised.
If you use named ranges of Database, Criteria and Extract then advanced filter will open with those ranges populating the form.

If still getting those errors then try doing a Filter-in-place rather than to a new location - if this still gives the errors then the problem is with your Database range - maybe some field (columns) without a fieldname (title)
OR if you can filter in place but not to a new location then you have specified an unknown or blank field in the Extract range.




Gavin