First VBA Macro - Run-time error 9: Subscript out of range

[theo23]

Hey MrExcel Community!

Long time browser here, you guys have basically taught me VBA 101(thanks!) but first post as I can't seem to find my specific problem in other threads. I'm an actuary, definitely not a coder, and am trying to make a simple button/program to keep a main 'Outstanding Trouble' tab updated with only transactions still marked as trouble in monthly tabs and remove when the trouble cell in the month's tab is unmarked. I already have the monthly tabs trouble columns corresponding to a boolean true/false column(P) and am trying to use those boolean values to copy/paste the range of the 'TRUE' rows(A thru O) into the main 'Outstanding Trouble' tab. I'm running into a runtime error "Subscript out of range" in my code, probably in my first For Each loop. Here's my code so far:

Option Explicit


Sub UpdateTrouble()


'maybe a command to clear entire Outstanding Trouble tab to avoid rewrites?

Dim Month As Worksheet
Dim rng As Range
Dim cell As Range
Dim srceRng As Range
Dim destRng As Range

'Begin the loop.
For Each Month In Worksheets

Set rng = Workbooks("WorkInProgress.xls").Sheets(ActiveSheet).Range("P2:P699")

'Pick out the "TRUE" trouble rows in each Month
For Each cell In rng

If cell.Value = "TRUE" Then
Set srceRng = Workbooks("WorkInProgress.xls").Sheets(ActiveSheet).Range("A" & ActiveCell.Row & ":O" & ActiveCell.Row)
'still need to set destRng to first available row in Sheet 1 instead of just A1
Set destRng = Workbooks("WorkInProgress.xls").Sheets("Outstanding Trouble").Range("A1")
srceRng.Copy
Workbooks("WorkInProgress.xls").Sheets("Outstanding Trouble").Paste destRng
Else: End If

Next

Next


End Sub

I'm sure it's an easy syntax patch but this is a slow learning process for me. Thanks a ton in advance for any help in the matter!

 

[theo23]

The tabs I want it to loop thru are labeled Jan, Feb, Mar, Apr, May, June, Jul, Aug, Sep, Oct, Nov, and Dec and are the 4th thru 15th tabs in my workbook currently titled 'WorkInProgress'. The master tab I want the trouble columns to pop into is the first tab in the workbook and labeled 'Outstanding Trouble', not sure if tab order matters. Thanks for all the help, you guys are great!

 

[theo23]

No errors but not functioning WHY??

Here's my latest attempt, most of it adapted straight from microsoft and examples on here. At least my clear portion works :/ any ideas where my loops are missing??

Option Explicit


Sub UpdateTrouble()


    'reset page to to blank to be re-filled in with below loops
    'At least this chunk works :p
    Dim clrRange As Range
    Set clrRange = ThisWorkbook.Sheets("Outstanding Trouble").Range("A2:O699")
    clrRange = ""
    
    Dim WS_Count As Integer
    Dim I As Integer
    'I is the index of my tabs. Months are 4-15
    Dim P As Integer
    'P is the row number of the trouble rows(in column P of each months tab)
    Dim cell As Range
    Dim srceRng As Range
    Dim destRng As Range
    Dim troubleRowNumber As Integer
              
    WS_Count = ThisWorkbook.Sheets.Count
    
    'Loop thru all tabs(months)
    For I = 4 To WS_Count
            
        'Pick out the "TRUE" trouble rows in each Month
        For P = 2 To 699
              
            If Cells(P, 16).Value = "TRUE" Then
                Set srceRng = ThisWorkbook.Sheets(I).Range("A" & P & ":O" & P)
        '   google says the line below makes destRng = first available row in the tab. This look right??
                Set destRng = ThisWorkbook.Sheets("Outstanding Trouble").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
                srceRng.Copy
                ThisWorkbook.Sheets("Outstanding Trouble").Paste destRng
            End If
            
        Next P
         
    Next I


End Sub

 

[Norie]

Re: No errors but not functioning WHY??

Does this work?

            If Cells(P, 16).Value = "TRUE" Then
                Set srceRng = ThisWorkbook.Sheets(I).Range("A" & P & ":O" & P)
        '   google says the line below makes destRng = first available row in the tab. This look right??
                Set destRng = ThisWorkbook.Sheets("Outstanding Trouble").Range("A" & Rows.Count).End(xlUp).Offset(1)
                srceRng.Copy destRng
            End If

 

[theo23]

Re: No errors but not functioning WHY??

Does this work?

            If Cells(P, 16).Value = "TRUE" Then
                Set srceRng = ThisWorkbook.Sheets(I).Range("A" & P & ":O" & P)
        '   google says the line below makes destRng = first available row in the tab. This look right??
                Set destRng = ThisWorkbook.Sheets("Outstanding Trouble").Range("A" & Rows.Count).End(xlUp).Offset(1)
                srceRng.Copy destRng
            End If

Nope :/ pardon a probably dumb question but in your code doesn't a .Copy need to be followed up with a .Paste ?