Weird ignoring of one item in a loop

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
 
A

Auric__

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.
[snip]

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

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.

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
 
G

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
 
A

Auric__

menken.john said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top