Excel macro generates Subscript out of Range error

Question

All three sheets will have the same column headings in row 1. On the first and second sheets in the workbook (titled "Monthly" and "Annual" respectively), I am using conditional formatting to color cells in columns Y and AC based upon there values as determined by a formula (Yellow if the calculation returns a value less than 90, Red if the value returned is 0 or a negative number). What I would like a macro to do is to copy the entire row from column A through column AD to a third sheet (titled "Maint Due"). It would also be nice if this process were automated so that anytime the values in columns Y and AC changed in the "Monthly" or "Annual" sheets, the information in the "Maint Due" sheet was automatically updated (but if I have to rerun the macro manually for that to happen it's not a big deal).

I've never used the Macro recorder and I can only figure out how to create a macro to copy and paste, so I had no luck there. After doing some more searching and watching some videos I cobbled this together:

Sub Show_on_Maint()
x = 2
'Sets the starting row

    Do While Cells(x, 2) <> ""
    'Continue to evaluate until a blank cell is reached
    If Cells(x, 25) <= 90 Then
        'Evaluates the cell in column Y to determine if the value 
        '     is less than or equal to 90
        Sheets("Monthly").Rows(x).Copy Sheets("Maint Due").Range("A2")
        'Copies the row to the Maint Due sheet
    Else
        If Cells(x, 29) <= 90 Then
            'Evaluates the cell in column AC to determine if the value 
            '     is less than or equal to 90
            Sheets("Monthly").Rows(x).Copy Sheets("Maint Due").Range("A2")
            'Copies the row to the Maint Due sheet
        End If
    End If
    x = x + 1
    Loop

End Sub

When I run/debug it I get a Loop without Do error. I think my logic is sound but I don't have enough experience to figure out why I'm getting that error.

EDIT: Fixed the missing End If before x = x + 1

Now I receive a runtime error 9 "Subscript out of range" at: Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")

EDIT: Fixed sheet names. Macro now runs without errors but doesn't appear to do anything. Also edited main post for brevity.

Could you please narrow down your question to just essential part and specify the problematic part? Thanks and regards, — Alexander Bell
I don't see Sheet1 and Sheet6 n your code where these names have came from, can you explain? — Paresh J

Alexander Bell Alexander Bell · Accepted Answer · 2014-12-19T18:14:21

You code snippet contains multiple error. The first 'Loop without Do error' has been fixed in comments. The second one, 'Subscript out of range' is self-descriptive, so check the maximum value of x+1 by adding

Debug.Print x = x + 1

to your loop. Also, make sure that you are not referencing a non-existing Worksheet (i.e. Sheets("Sheet6")) in your copy statement.

Hope this will help.

Excel macro generates Subscript out of Range error

1 Answers