Advanced Filter Macros - Inconsistant Results

[TheScott]

Back again. I have spent the better part of a week trying to work through and understand this problem but, as you may guess, I am little the wiser.

I am attempting to implement Advanced Filter routines using macros. Sometimes it works…. Sometimes it doesn’t. I can copy a workbook with a working filter and the filter in the new workbook will not work. I can create the same macro for two different places in the same workbook… one will work and one won’t. Always the Date/Advanced Filter function works manually. I have tried every bit of code that I can find and I can get consistent results. I don’t seem to find clear answers even though this appears to be a reoccurring question. Either I am the problem (likely) or Excel is (not unlikely).

At any rate can anyone show me what I am doing wrong and how to get a consistent filter for the data presented here?

Thanks
Scott

FilterProblem.xlsm

[Kaper]

1) advanced filter can copy the results only to the same sheet where data is located. It is working for you when you use structural addressing, because although data is in another sheet excel picks it up not directly from the sheet but with this structural address (so excel is in a sense fooled, where data really sits). Everything works fine if the results go to the same sheet as source:

Please Login or Register  to view this content.

2) using same names for procedures in different modules (normal and worksheet) is usually just "asking for trouble" - do it anly when you need.
3) it is good to use sheet_qualified references to ranges. Sheetx.range("someaddress") or sheets("Sheet name").range("someaddress") basically alvays, and expecially as you are working with more than one sheet. Unqualified reference range("someaddress") is interpreted as activesheet.range("someaddress")

Hope it gives some insight into your tests. see attachment with changes in your procedures (note that AZ1 and right/down in Lists is not cleared so clear manually to see the results)

[TheScott]

Kaper,

Thank you for responding. I was beginning to think that either my question / example or both was not understandable and no one was going to respond… at any rate, I thank you.

1.My understanding with regards to copying was that ‘advanced filter’ only needed to be executed from the location(sheet) where the criteria was and that copying would reference that executing sheet…. My understanding anyway. Perhaps this is only a function of the ‘Advanced Filter’ control on the ribbon and not VBA code.

2. I put the sheet together in a hurry but can see that creating two procedures with the same name can be a problem… point taken.

3. This is where it gets sticky for me and the point of my confusion. I compared the Sub CashFilter_Click that resides both in the’ list’ sheet and in a module between my original file and your Zesty1 file. Yours and mine are identical except for the Debug.print statement, yet yours works and mind doesn’t. The answer to this question could clear a lot of fog for me because as I said, I have created workbooks where I have success and others where nothing works. Can you explain why your CashFilter_Click() operates differently than mine?

Your assistance is much appreciated as I must be missing some basic concept of either the code or the filtering process.

Kind Thanks
Scott

[Kaper]

Spot the Easter Egg (we have such traditional folk game in Easter Monday :-) ):

Please Login or Register  to view this content.

As the procedure is in Sheet2 (Lists) the unqualified references are to Sheet2.

[TheScott]

Well... Perhaps not exactly identical. I'm thinking the Easter bunny hid that egg AFTER I looked.... I mean, I am sure I looked there!

Have a very pleasant Easter weekend and thank you again

Scott

After Thought: It was my understanding that a named range is global to the workbook, Why would a table name be considered unqualified?

[Kaper]

If calling from general module - I'd assume qualification is indeed not needed and table name is global, but here you were calling not from the workbook level, but from the sheet level.

Im not really sure, but as it works, then perhaps ...