+ Reply to Thread
Results 1 to 4 of 4

Regression Macro to Run on All Sheets, Excluding Some

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    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

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,434

    Re: Regression Macro to Run on All Sheets, Excluding Some

    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

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Regression Macro to Run on All Sheets, Excluding Some

    Duplicate post at http://www.excelforum.com/excel-prog...ml#post4315449
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,434

    Re: Regression Macro to Run on All Sheets, Excluding Some

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

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Looping through sheets to clear a specified range, but excluding some sheets
    By d.sanchez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2013, 02:39 PM
  2. [SOLVED] Copy All Visible Sheets To New Workbook Excluding Specific Sheets
    By ezrizer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-19-2012, 02:19 PM
  3. Copy specific cell from multiple sheets to summary - excluding some sheets
    By kabammi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2012, 09:27 AM
  4. regression macro on multiple sheets
    By Garryk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 07:57 PM
  5. Print visible sheets excluding certain ones
    By amelio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-09-2011, 12:17 PM
  6. excluding sheets from SUM formula
    By kmfdm515 in forum Excel General
    Replies: 8
    Last Post: 01-22-2008, 12:53 PM
  7. regression lines and r-square in work sheets instead of charts
    By Mathematically challenged in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-01-2005, 10:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1