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!

 

[Rick Rothstein]

Set srceRng = Workbooks("WorkInProgress.xls").Sheets(ActiveSheet).Range("A" & ActiveCell.Row & ":O" & ActiveCell.Row)

In the above code... ActiveCell can only apply to the active worksheet in the active workbook... there is no ActiveCell on any other sheet whether in the current workbook or any other workbook. I am not sure what you are trying to do, but my guess is this is at the heart of your problem.

 

[Norie]

Does it work if you replace Sheets(ActiveSheet) with ActiveSheet?

Set rng = Workbooks("WorkInProgress.xls").ActiveSheet.Range("P2:P699")
' ...
Set srceRng = Workbooks("WorkInProgress.xls").ActiveSheet.Range("A" & ActiveCell.Row & ":O" & ActiveCell.Row)

 

[theo23]

Does it work if you replace Sheets(ActiveSheet) with ActiveSheet?

Set rng = Workbooks("WorkInProgress.xls").ActiveSheet.Range("P2:P699")
' ...
Set srceRng = Workbooks("WorkInProgress.xls").ActiveSheet.Range("A" & ActiveCell.Row & ":O" & ActiveCell.Row)

Nope, still same "Subscript out of range" error here's where I'm at and easier on the eyes than my original post:

Option Explicit


Sub UpdateTrouble()


    'maybe need to clear entire Outstanding Trouble tab?
    
    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").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

 

[Norie]

Do you have a workbook with the exact name 'WorkInProgress.xls' open when you run the code?

Is that the workbook the code is in?

 

[theo23]

Do you have a workbook with the exact name 'WorkInProgress.xls' open when you run the code?

Is that the workbook the code is in?

That is the workbook the code is in. Actually was saved as a .xlsx file(2013) but when I change all the .xls ones to .xlsx I get error '13' Type mismatch. I get the same error when I delete all the suffix's here's where it stands:

Option Explicit


Sub UpdateTrouble()


    'maybe need to clear entire Outstanding Trouble tab?
    
    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.xlsx").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.xlsx").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.xlsx").Sheets("Outstanding Trouble").Range("A1")
            srceRng.Copy
            Workbooks("WorkInProgress.xlsx").Sheets("Outstanding Trouble").Paste destRng
            Else: End If
            
        Next
         
    Next


End Sub

 

[Norie]

What happens if you use ThisWorkbook?

Option Explicit

Sub UpdateTrouble()

    'maybe need to clear entire Outstanding Trouble tab?
    
    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 = ThisWorkbook.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 = ThisWorkbook.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 = ThisWorkbook.Sheets("Outstanding Trouble").Range("A1")
                srceRng.Copy
                ThisWorkbook.Sheets("Outstanding Trouble").Paste destRng
            End If
            
        Next
         
    Next

End Sub

 

[theo23]

What happens if you use ThisWorkbook?

Option Explicit

Sub UpdateTrouble()

    'maybe need to clear entire Outstanding Trouble tab?
    
    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 = ThisWorkbook.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 = ThisWorkbook.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 = ThisWorkbook.Sheets("Outstanding Trouble").Range("A1")
                srceRng.Copy
                ThisWorkbook.Sheets("Outstanding Trouble").Paste destRng
            End If
            
        Next
         
    Next

End Sub

No errors! :D just didn't perform the copy/paste function either haha will toy around with it some more and report back

 

[Kamolga]

No errors! :D just didn't perform the copy/paste function either haha will toy around with it some more and report back

Maybe try to take out the "" around TRUE -> Excel might read as bolean

if cell = true then

Can we just say Next? I always stipulate Next cell , Next Month

 

[theo23]

Maybe try to take out the "" around TRUE -> Excel might read as bolean

if cell = true then

Can we just say Next? I always stipulate Next cell , Next Month

No difference, for whatever it's worth my P column True/False values are strings from an IF function, not intrinsically boolean lol. Also could the name of my tabs be the reason it's not looping/catching any of the TRUE trouble cells?