Weird ignoring of one item in a loop
 

Hi, I'm using Excel 2010 on Windows 7 and I have created a loop that goes through each worksheet in the workbook and performs several routine operations. For some strange reason it ignores my line about turning off the autofilter. When I run the macro it resets the cursor to A1 and eliminates any marching ants but the AutoFilter on several sheets remains 'on.' Can anyone tell me what I might be doing wrong? I've included the code below. Many thanks.

Weird ignoring of one item in a loop
 

menken.john wrote:

Hi, I'm using Excel 2010 on Windows 7 and I have created a loop that
goes through each worksheet in the workbook and performs several routine
operations. For some strange reason it ignores my line about turning off
the autofilter. When I run the macro it resets the cursor to A1 and
eliminates any marching ants but the AutoFilter on several sheets
remains 'on.' Can anyone tell me what I might be doing wrong? I've
included the code below. Many thanks.
[snip]

Just a thought, don't check ActiveSheet.AutoFilterMode, just set it = False.

--
It's like God is pointing at me saying,
"THOU SHALT NOT GET ANY TONIGHT THOU WANTON WENCH!"
**** you, God... **** you.

Weird ignoring of one item in a loop
 

On Fri, 9 Nov 2012 11:14:15 -0800 (PST), wrote:

Hi, I'm using Excel 2010 on Windows 7 and I have created a loop that goes through each worksheet in the workbook and performs several routine operations. For some strange reason it ignores my line about turning off the autofilter. When I run the macro it resets the cursor to A1 and eliminates any marching ants but the AutoFilter on several sheets remains 'on.' Can anyone tell me what I might be doing wrong? I've included the code below. Many thanks.

Code:

---


'Reset the cursor to cell A1 on all sheets, turn any autofilters off
'and lose any marching ants that might remain

    Dim i As Integer

    For i = 1 To Sheets.Count
        With Worksheets(i)
           
            Range("A1").Select
            If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
            Application.CutCopyMode = False  'This gets rid of any marching ants that may remain
         
        End With
    Next i




---

Without testing, you probably just need to remove the reference to ActiveSheet (but leave the dot after).


Dim i As Integer

For i = 1 To Sheets.Count
With Worksheets(i)

Range("A1").Select
If .AutoFilterMode = True Then .AutoFilterMode = False
Application.CutCopyMode = False 'This gets rid of any marching ants that may remain

End With
Next i

Weird ignoring of one item in a loop
 

I agree with Auric_! You don't have to do all that checking (just slows
everything down). Just set the property to *= False*.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Weird ignoring of one item in a loop
 

And he don't have to select A1 on each sheet.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Weird ignoring of one item in a loop
 

Thanks Auric, that worked great.
I appreciate the help.

Weird ignoring of one item in a loop
 

menken.john wrote:

Thanks Auric, that worked great.
I appreciate the help.

Good to hear, but see also Ron Rosenfeld's post. He caught a basic detail
that I didn't think of.

--
It's not just 3 minutes of my life,
it's *another* ****ing 3 minutes of my life!