Macro filtering all dates out, not leaving ones required.

[Ochimus]

Attached Macro should find all records on the DATABASE SHEET that meet three Criteria (Sample Point, Category and Measure) between a Start and End date set on the MEASURE sheet.

But it filters out ALL the DATABASE dates, generating a RunTime 1004 error message when the Code then tries to copy the (incorrectly) filtered range to the MEASURE sheet,

Please Login or Register  to view this content.

Hope someone can see the error?

All solutions, suggestions and alternatives welcome

Ochimus

[CK76]

Few things.
1. Sheet3.Select & Selection.Autofilter
Avoid using Selection in code. Unless it's absolutely necessary. As it's prone to error and causes slowdown in code. Also, AutoFilter shouldn't be applied to sheet. But to range.

2. Rows.Count doesn't need "." in front.

3. Your criteria range reference is off. Below should be looking at C column. Not D, as per your worksheet. As well, C6 isn't exact match to column value (field 3). You'll need to concatenate with wild card.

Please Login or Register  to view this content.

Finally, I'd recommend using Advanced Filter Copy rather than Autofilter for this sort of data extraction.
Ex:

Please Login or Register  to view this content.

See attached set up.

[JLGWhiz]

Your filter criteria is referencintg the wrong cells on sheet1.

[xladept]

I'll post the treated sample next #.

[xladept]

Here it is:

[Ochimus]

Many thanks to everyone who responded so quickly and effectively.

Astounding that in all the years of using Excel I have not come across Advanced Filter Copy before - so that's another technique to master!

Can now mark this as Solved, and on to the next challenge.

Ochimus