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:

Please Login or Register  to view this content.

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 Sub

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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