Criteria range for advance filter not working.

[noname91]

Heya,

I'm trying to copy data thru VBA. Specifically, I want to copy only those rows whose corresponding cell in the date column (column "A") has the month text that I'm looking for in a specific cell range - the Criteria Range. In this case, it'd be cell "Z1". If "Z1" says April, then only copy those rows whose date column contained a corresponding date containing the string "April". The macro is run from a macro file with a button and a field where the month name is entered. The copying is done from a separate file.

Right now, the copying seems to be working fine but regardless of what I type in Z1 as the Criteria Range, I still get ALL rows. I'm not sure what the issue is.

Here is the code. I'm trying to solve something that was left over from a colleague who left, and they didn't comment, nor apply proper naming conventions but I still hope it will be readable. I will definitely correct this as soon as this issue is resolved.

Please Login or Register  to view this content.

Any help would be appreciated. Thanks!

[lecxe]

Hi

Please Login or Register  to view this content.

The criteria range should have at least 2 cells, like Z1:Z2 with name of the field in Z1 and the criterion in Z2.

[noname91]

Thanks for the reply lecxe!

I see. I've made the changes to the code, but unfortunately I'm still getting all rows copied instead of only the rows that should contain the month in question. I'm now using rows Y1:Y2 where Y1 is "Date" and Y2 is the month text.

Here is the updated code:

Please Login or Register  to view this content.

[lecxe]

Hi

Please Login or Register  to view this content.

Seems perfectly OK to me.

I can only think that maybe the criterion in Y2 is not correct.

It would help if you'd post a workbook with a small sample. This would allow us to test your code.

[noname91]

Ah, I see. Wasn't sure if it was an issue with the syntax or something else on my end. In any case, I redid the whole thing from scratch with a somewhat different approach and the Criteria Range looks like its working as expected now.

Not sure what the issue was, might trouble shoot on my end later on.

Thanks for the clarifications!

[lecxe]

You're welcome.