Macro to unfilter data from a certain sheet

[howard]

I have written code to unfilter data from sheets "Sales1" onwards

 Sub Unfilter_Sheets()

Dim xWs As Worksheet
Dim i As Long
For i = Sheets("Sales1").Index To Worksheets.Count
For Each wks In ThisWorkbook.Worksheets(i)
On Error Resume Next
If wks.AutoFilterMode Then
wks.AutoFilterMode = False
Next wks
End If


End Sub

I get run time error "Next with for"

It would be appreciated if someone could amend my code

 

[Peter_SSs]

You just need to decide if you trying to do from 'Sales1' to the end of the workbook or every sheet in the workbook?

For i = Sheets("Sales1").Index To Worksheets.Count
For Each wks In ThisWorkbook.Worksheets(i)

You also need to put your If .. Then
End if

block inside your For...Next loop, not half in and half out.

If you took the trouble to indent your blocks of code like I showed you recently, you wouldn't be having most of these sorts of problems.

 

[howard]

Thanks for your input Peter

I have amended my code as follows:

 Sub Unfilter_Sheets()

Dim xWs As Worksheet
Dim i As Long
For i = Sheets("Sales1").Index To Worksheets.Count
On Error Resume Next
With Sheets(i)
If .AutoFilterMode Then
End If
.AutoFilterMode = False
End With
Next i

End Sub

 

[Peter_SSs]

have amended my code as follows:

It is better but not quite correct - or at least not as I think you intended. If you could indent it in blocks as shown before & post it again, I could advise you further.

 

[howard]

Hi peter

have indented code in blocks-hope this is what you meant

 Sub Unfilter_Sheets()

Dim xWs As Worksheet
Dim i As Long
  
  For i = Sheets("Consolidated Auto").Index To Worksheets.Count
  On Error Resume Next
    
   With Sheets(i)
  
  If .AutoFilterMode Then
     End If
  .AutoFilterMode = False
   End With
  
Next i
                 
 
End Sub

 

[Peter_SSs]

hope this is what you meant

No, that's not what I mean. Each section of code that has a start line and an end line should start and finish with the same level of indentation and everything between that start line and end line should be indented further than the first and last lines of that section.

Sections that have start/end lines are sections like

Sub
End Sub

For
Next

If
End If

With
End With

Look at the marked image in the post I linked to from post #2 above and see how every one of those red brackets I drew link the first/last line of a section of code and each of those sections start and end with the exact same indentation in from the left and all the code between the start/end of each section is indented further. This makes it easy to see where the sections start and finish and if any start or finishes are missing or if there are any "crossed lines" like in post #7 of that linked thread.

In your post #5 here
Sub and End Sub are not indented the same
Some code lines between Sub and End Sub are not indented further than those two lines
For i and Next i are not indented the same as each other
If and End If are not indented the same as each other
etc

Whilst such formatting does not affect the running of the code, it does affect the ease of reading and debugging code & I'm sure is partly why you keep making posts that say "My code is giving an error. Please fix it". If you formatted neatly & consistently you would be able to see many of the problems yourself.

 

[howard]

Thanks for your valuable input which makes far more sense now

 

[Peter_SSs]