-1

I have a workbook which will consist of an inconsistent number of worksheets (so could be <10 or >100 at any given time).

The values column will always be in column G on each and every worksheet. I have written the following to achieve a total at the bottom of column G in each worksheet:-

Sub WorksheetLoop()

    Dim WS_Count As Integer
    Dim I As Integer
    Dim LR As Long

    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 1 To WS_Count

        LR = Range("G" & Rows.Count).End(xlUp).Row
        Range("G" & LR + 1).Formula = "=SUM(G4:G" & LR & ")"
        Worksheets(ActiveSheet.Index + 1).Select

    Next I

End Sub

It runs, however once the final worksheet has been totalled, I get the following runtime error:-

Run-time error '9': Subscript out of range

I've been looking at other threads and applying different approaches, some also work but trigger a runtime error 91.

If I place a end or exit to the for statement it fails after processing just one calculation.

I believe the issue lies in Worksheets(ActiveSheet.Index + 1).Select and falling over when there is no activesheet to process.

user10743540
  • 3
  • 1
  • Once you get to the last worksheet in the worksheets collection, trying `worksheets(ActiveSheet.Index + 1).Select` is trying to select something that doesn't exist. –  Dec 04 '18 at 11:07
  • Thank you norbjd. I tried a number of titles and this was the only one which was accepted. – user10743540 Dec 04 '18 at 15:38

2 Answers2

1

Worksheets in a workbook are a Collection. The good thing about collections is that you can step through each item in the collection using a For...Each loop.

The code below will step through each Worksheet in the Worksheets collection in the workbook that contains the code (ThisWorkbook). You can change this to ActiveWorkbook which will do the same for whichever workbook happens to be active at the time.

Rather than return the row number of the last cell in column G the code returns a reference to the actual cell and then Offsets by one row to get the next blank row.

R1C1 style is used for range referencing - R4C means row 4 in whichever column the formula is. R[-1]C means the row above the formula in whichever column the formula is in. So R4C:R[-1]C placed in cell G8 would mean G4:G7. 

Sub WorkSheetLoop()

    Dim wrkSht As Worksheet
    Dim rLastCell As Range

    For Each wrkSht In ThisWorkbook.Worksheets
        Set rLastCell = wrkSht.Cells(wrkSht.Rows.Count, 7).End(xlUp)
        If rLastCell.Row > 4 Then
            rLastCell.Offset(1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
        End If
    Next wrkSht

End Sub

Why your version doesn't work
The reason why you're getting runtime error #9 is because your code is trying to select a sheet that doesn't exist.

For example, your code starts with the first sheet selected in a two sheet workbook:

Loop 1:

  • LR is found for the first sheet.
  • The SUM formula is added to the first sheet.
  • The second sheet is selected.

Loop 2:

  • LR is found for the second sheet.
  • The SUM formula is added to the second sheet.
  • The code attempts to select the third sheet which doesn't exist - error occurs.

Your code would work if you removed the Worksheets(ActiveSheet.Index + 1).Select line and added Worksheets(I).Select as the first line in your loop.

Also, your code can start with any sheet selected - if the last sheet is active when the code starts then it will add the sum to that sheet and then try and select the next sheet causing it to fail on the first loop.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • 1
    Thank you for taking the time to explain this. I will give this a go and get back to you. – user10743540 Dec 04 '18 at 15:43
  • This piece of advice made my code work as I expected it to work; thank you again for the advice and explanation. "Your code would work if you removed the Worksheets(ActiveSheet.Index + 1).Select line and added Worksheets(I).Select as the first line in your loop." – user10743540 Dec 06 '18 at 12:58
  • It's generally better to avoid `Select` & `Active...`. It reduces the amount of screenflicker (to a point where you don't really need `Application.ScreenUpdating=False`) and it doesn't matter what sheet/book/range is selected when the code starts. – Darren Bartrup-Cook Dec 06 '18 at 13:19
0

Once you get to the last worksheet in the worksheets collection, trying worksheets(ActiveSheet.Index + 1).Select is trying to select something that doesn't exist.

Since there is no actual reason to select the worksheet to add a formula, avoid using Select.

Dim I As Integer, LR As Long

For I = 1 To ActiveWorkbook.Worksheets.Count

    with ActiveWorkbook.Worksheets(I)
        LR = .Range("G" & .Rows.Count).End(xlUp).Row
        .Range("G" & LR + 1).Formula = "=SUM(G4:G" & LR & ")"
    end with

Next I