Worksheets("NamedRange").ShowAllData Runtime error in 2007 but not 03

**TKshelton** · 01-09-2010, 10:39 AM

Hi,

I am somewhat new to vba. I have this code that works in Excel 2003.

Please Login or Register  to view this content.

But fails during compile in Excel 2007 with a Runtime error 1004 for "ShowAllData method of Worksheet class failed" with the following highlighted during debug "Worksheets("SystemInfoReport").ShowAllData"

I have the workbook opened in 'compatablity mode' since it must now work in a 2003 environment, but occasionally do work like this offsite in a 2007 environment.

I have googled and attempted to read up on this but still come up empty, so I would appreciate any help/insights.

**royUK** · 01-09-2010, 10:58 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Added this time,but read the rules before posting again

**TKshelton** · 01-09-2010, 11:34 AM

Thank you. My apologies. I have read and understand the rules for tagging. This is my first attempt at asking for help via any forum, so thank you for your patience.

**TKshelton** · 01-09-2010, 12:12 PM

It appears I may have figured out the issue, and it may not relate to 03/07 differences - but rather I was causing it inadverdently.

What you can't see in the code is that the filters on this worksheet are applied to a limited range .

As I have worked with this macro and then troubleshot this problem, I have had a habit of selecting a cell outside of this range . . . so the active cell is outside of the range of the filters. When this is true, I can consistently cause the error. When I change the selection to within the filtered range and then run the macro, the error goes away. What threw me off for awhile is by protecting the sheet before running the macro I would also clear the problem - even if I had selected a cell outside of the range of the filters . . . it looks like protecting the worksheet removes the cell selection as well.

I don't totally understand why this is happening, but would think it is because the sheet is not in filter mode when a cell outside of the filter range is selected . . . even though the arrows and filtering remains active.

Unless I get other problems later, this should not be a case that I need to protect against since normally the worksheet is protected and prevents a user from doing what I was doing.

Hope this helps someone in the future and I would appreciate any replies that added additional insights. I am learning.