Weird ignoring of one item in a loop

  • Thread starter Thread starter menken.john
  • Start date Start date
M

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.

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
 
menken.john said:
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.
Click to expand...
[snip]

Just a thought, don't check ActiveSheet.AutoFilterMode, just set it = False.
 
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
 
menken.john said:
Thanks Auric, that worked great.
I appreciate the help.
Click to expand...

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