How to run multiple VBA procedures on multiple sheets at once, except for one sheet-CodePudding

I'm trying to run the same set of procedures on all worksheets at once except for the a sheet titled "summary". This is what I've tried, and I keep getting different types of errors. The procedure I'm calling is a procedure that calls other procedures, maybe that is the issue?

Option Explicit
Sub runAll()
  
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "summary" Then
       
     Call Formatting

    End If
Next ws

End Sub

Contained in the Formatting sub are more calls.

Sub oracleCogsUniversal()
Call Formatting
Call DeleteDetails
Call addSummary
Call pasteID
Call moveUp
Call formatFile
End Sub

CodePudding user response：

You'll need to debug this. First thing I would do is this:

    Option Explicit

    Sub runAll()        
       Dim ws As Worksheet
       For Each ws In ThisWorkbook.Worksheets
          Debug.print ws.name  
       Next ws 
    End Sub

to make sure your sheet names are as you expect. Open the Immediate Window and see what is output. Chances are the name is not "summary" as you expect. If it is not, change your <> "summary" statement to the correct name. Once that is worked out, in your "Formatting" function, remark everything out and reenable each line, or section, one at a time until you find the error.

The only way to get to the bottom of this is to debug it systematically.

CodePudding user response：

In runAll you only call Formatting - did you mean to call oracleCogsUniversal? You don't pass anything to those sub-procedures, so I'm guessing there's no way for them to know which sheet they're supposed to be operating on?

You should be passing ws to oracleCogsUniversal, and that in turn should maybe be passing that worksheet reference on to anything else it calls, so the called code can work on a specific worksheet.

Option Explicit

Sub runAll()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "summary" Then
           oracleCogsUniversal ws     '<< pass along the `ws` reference; use of Call is deprecated
        End If
    Next ws
End Sub

Sub oracleCogsUniversal(ws As Worksheet) '<< get `ws` from `runAll`
    Formatting ws                        '<< pass ws to called Sub
    DeleteDetails ws
    addSummary ws
    pasteID ws
    moveUp ws
    formatFile ws
End Sub


Sub Formatting(ws As Worksheet)
    ws.Rows(1).Font.Bold = True 'work directly on `ws`
End Sub