Weird ignoring of one item in a loop

[menken.john]

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.

'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

 

[Auric__]

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.

 

[Ron Rosenfeld]

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.

'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

 

[GS]

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

 

[Claus Busch]

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


Regards
Claus Busch

 

[menken.john]

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

 

[Auric__]

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.