Running Macro on all sheets that meet cell criteria

[heyfraank]

Hi, I'm trying to make a macro run on every sheet in my workbook, problem is macro doesn't work if cell value is 0:

Sub Ola()
    Dim Rng As Range
    Dim InputRng As Range, OutRng As Range
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Range("A1:B1")
    Set OutRng = Range("B3")
    Set OutRng = OutRng.Range("A1")
    For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
Next
End Sub

I tried to loop it using:

Sub manekankit()
   Dim Ws As Worksheet
   
   For Each Ws In ActiveWorkbook.Worksheets
      If Ws.Range("B1").Value > 0 Then
         Ola
      End If
   Next Ws
End Sub

But it only runs on the active worksheet, not on the whole workbook, so I've got two problems:

1) Every sheet has a Name on B1 and Number of Payments on cell B1, I only need to run the macro on sheets that have a number of payments greater than 0
2) First sheet named "ACUMULADOS" has a summary table of every payment, so I also need to exclude the macro from running on this sheet.

Thanks, any help is appreciated.

 

[Joe4]

Welcome to the Board!

Try this:

Sub manekankit()
   Dim Ws As Worksheet
   
   For Each Ws In ActiveWorkbook.Worksheets
      If (Ws.Range("B1").Value > 0) And (Ws.Name <> "ACUMULADOS") Then
         Ws.Activate
         Ola
      End If
   Next Ws
End Sub

 

[heyfraank]

Welcome to the Board!

Try this:

Sub manekankit()
   Dim Ws As Worksheet
  
   For Each Ws In ActiveWorkbook.Worksheets
      If (Ws.Range("B1").Value > 0) And (Ws.Name <> "ACUMULADOS") Then
         Ws.Activate
         Ola
      End If
   Next Ws
End Sub

Thank you!, works like a charm, very helpful.

 

[Joe4]

You are welcome!

The important thing to note that when looping through worksheets, each worksheet is not actually selected.
So whenever your condition was met, your code would run against the sheet that was activate at the top you called the macro to run (so it literally would be running the code over-and-over on the same sheet).

The way to get around that is to use sheet references in your range references in your OLA code, or simply select the sheet before calling it to make that the active sheet (which is what we did here).

 

[heyfraank]

Nice, many thanks for the tip