0

My if statement is working on the active spreadsheet.

The loop continues to loop through the same active sheet instead of looping through all the spreadsheets within a workbook.

Sub WorksheetLoop()
    Dim WS_Count As Integer
    Dim I As Integer
        
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    ' Begin the loop.
    For I = 1 To WS_Count
    'For Each Ws In Workbooks
        
        If WorksheetFunction.Sum(Range("N:N")) > 20000 Then
            Range("A:P").AutoFilter Field:=7, Criteria1:="<> 0
        End If
               
    Next I
    
End Sub

This VBA code is supposed to loop through all the sheets. If the sum of column N is greater than 20,000, then column G will filter out "0:. Next, It should loop through the next spreadsheet. If column N is not greater than 20000, then I want it to move on to the next spreadsheet without doing anything.

I tried a for each loop and I got the same results as the for loop.

Community
  • 1
  • 1
Sophie101
  • 9
  • 2

3 Answers3

0

Try this

Private Sub WorksheetLoop()
    Dim ws As Worksheet
    Dim WS_Count As Integer
    Dim I As Integer
    
    WS_Count = ActiveWorkbook.Worksheets.Count
         ' Begin the loop.
    For I = 1 To WS_Count
    'For Each Ws In Workbooks
        Set ws = ActiveWorkbook.Worksheets(I)
        If Application.WorksheetFunction.Sum(ActiveWorkbook.Worksheets(I).Range("N:N")) > 20000 Then
            ws.Range("N:N").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:="<>0" 
        End If
    Next I

End Sub
ValNik
  • 1,075
  • 1
  • 2
  • 6
  • Thank you for providing a script. I copied and pasted what you provided by it gave me the "Run-time error '1004' Unable to get the sum property of the worksheet Function Class. I think it has something to do with the "ws." in front of range. When I remove the ws. It doens't loop through the remaining tabs. – Sophie101 Apr 24 '23 at 23:14
  • Some edit. pls try. – ValNik Apr 24 '23 at 23:43
  • I'm getting the same error code with the new edits. It's highlighting the same row that it had previously highlighted. – Sophie101 Apr 24 '23 at 23:55
  • Couldn't reproduce the error. Check in any way, by example in "Immediate window" calculation WorksheetFunction.Sum(ActiveWorkbook.Worksheets(1).Range("N:N")). Also chek Field parameter value of Autofilter – ValNik Apr 24 '23 at 23:56
  • Reproduce error: if range contains #Error, or #div/0 or other sach value - you get this message. If select this range in worksheet, excel du not show sum of values in status bar, only count of cells – ValNik Apr 25 '23 at 00:13
  • Thank you so much for your help! I got the Code to work. Have a great day! I spent 6 + hours trying to figure out this simple code. I'm new to VBA, so I'm hoping things won't take as long the more I get the hang of this. – Sophie101 Apr 25 '23 at 00:26
  • I wish you all success : ) – ValNik Apr 25 '23 at 00:44
0

Filter All Worksheets 'At Once'

Option Explicit

Sub ApplyFilters()
    Const PROC_TITLE As String = "Apply Filters"
    On Error GoTo ClearError ' start error-handling routine
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet, Total
    
    For Each ws In wb.Worksheets
        With ws
            If .FilterMode Then .ShowAllData ' clear filters
            If .AutoFilterMode Then .AutoFilterMode = False ' avoid wrong cols.
            With .Range("A1").CurrentRegion ' assumes no empty rows or columns
                'Debug.Print .Address(0, 0)
                If .Rows.Count > 1 Then
                    Total = Application.Sum(.Columns("N") _
                        .Resize(.Rows.Count - 1).Offset(1))
                    If IsNumeric(Total) Then
                        If Total > 20000 Then
                            .AutoFilter 7, "<>0"
                        End If
                    End If
                End If
            End With
        End With
    Next ws

    MsgBox "Filters applied.", vbInformation, PROC_TITLE

ProcExit:
    Exit Sub
ClearError: ' continue error-handling routine
    MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf _
        & Err.Description, vbCritical, PROC_TITLE
    Resume ProcExit
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
-1

Add a line to activate corresponding sheet at each loop:

WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
    For I = 1 To WS_Count
       'For Each Ws In Workbooks
           ActiveWorkbook.Sheets(I).Activate
           If WorksheetFunction.Sum(Range("N:N")) > 20000 Then
               ActiveSheet.Range("A:P").AutoFilter Field:=7, Criteria1:="<> 0    
           End If
     Next I

Hope it helps, bye

Alio
  • 1
  • 1
  • 1