Regression Macro to Run on All Sheets, Excluding Some

Hello everyone,

New to the forums and relatively new to the Macro/VBA world. I'm working on a macro which runs regression analysis on a specific X/Y column on multiple tabs of data and drops the regression table output into each sheet. I have the following code working, but it's too clunky and I'd like to adjust the code to exclude specific sheets:

Sub Regression()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
'
' Regression Macro
'
' Keyboard Shortcut: Ctrl+r
'
Application.Run "ATPVBAEN.XLAM!Regress", s.Range("$L$14:$L$43"), _
s.Range("$P$14:$P$43"), True, False, , s.Range("$F$47") _
, False, False, False, False, , False
Next s


End SubHere is my attempt at editing the code to run and exclude specific sheets:

Sub Regression()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each s In ActiveWorkbook.Worksheets
If sh.Name = "0. Hedge Index" Or sh.Name = "1. Effectiveness Assessment" Or sh.Name = "IRS All Valuation Data" Or sh.Name = "Swap Key" Or sh.Name = "Immaterial FV at 2.1 -Bloomberg" Or sh.Name = "Tickmarks" Then
sh.Activate

' Regression Macro
'
' Keyboard Shortcut: Ctrl+r
'
Application.Run "ATPVBAEN.XLAM!Regress", s.Range("$L$14:$L$43"), _
s.Range("$P$14:$P$43"), True, False, , s.Range("$F$47") _
, False, False, False, False, , False
Application.ScreenUpdating = True
End If
Next s
End Sub

I'm getting a 424 error, object required, on my 'if statement' above. Any help is greatly appreciated!

Thanks very much,

-Spencer

Hi there,

Try using the following version of your code:

Option Explicit


Sub Regression()

    Dim wks As Worksheet

    Application.ScreenUpdating = False

    For Each wks In ActiveWorkbook.Worksheets

        If wks.Name = "0. Hedge Index" Or _
           wks.Name = "1. Effectiveness Assessment" Or _
           wks.Name = "IRS All Valuation Data" Or _
           wks.Name = "Swap Key" Or _
           wks.Name = "Immaterial FV at 2.1 -Bloomberg" Or _
           wks.Name = "Tickmarks" Then

            wks.Activate    '   <<<  This statement might not be required

'           Regression Macro
'           Keyboard Shortcut: Ctrl+r
            Application.Run "ATPVBAEN.XLAM!Regress", _
                             wks.Range("$L$14:$L$43"), _
                             wks.Range("$P$14:$P$43"), True, False, , _
                             wks.Range("$F$47"), False, False, False, False, , False

        End If

    Next wks

    Application.ScreenUpdating = True

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks for pointing this out, otherwise he'd have gone on my "Can't Be Bothered To Reply" blacklist!

Regards,

Greg M