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

theo23

New Member
Joined
Sep 8, 2015
Messages
17
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

New Member
Joined
Sep 8, 2015
Messages
17
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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

theo23

New Member
Joined
Sep 8, 2015
Messages
17
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??

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Re: No errors but not functioning :( WHY??

Does this work?
Code:
            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

New Member
Joined
Sep 8, 2015
Messages
17
Re: No errors but not functioning :( WHY??

Does this work?
Code:
            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 ?
 

Forum statistics

Threads
1,141,292
Messages
5,705,529
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top