Regression Macro to Run on All Sheets, Excluding Some

[sfostrom]

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

[FDibbins]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.

http://www.excelforum.com/excel-prog...in-sheets.html