VBA formula using sheet number instead of sheet name

Question

I am using VBA to write a Macro and it is working exactly as I want, except that I would like my formulas to loop through the sheets instead of using data on 'SAFO-1', 'SAFO-1' refers to the fish Salvelinus fontinalis (SAFO). I have many fish species (e.g., Morone saxatilis (MOSA)) and it would be way more pratical if I could refer to the sheet number instead of their name. Unfortunately, I do not decide sheet names and they have to stay as they are because we're working on shared projects with unique name for every samples. Sheets name change between projects and I want to be able to use my code in all of them. Here is my current code:

Sub Mean()
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim Sheet As Integer
    
    k = 4
    i = Application.Sheets.Count
    
    For Sheet = 2 To i
        Worksheets(Sheet).Select
        j = 3
            Do While ActiveCell.Value <> "0"
                Range("A" & j).Select
                If ActiveCell.Value = "0" Then
                    Range("A1").Copy
                    Worksheets("Mean").Range("A" & Sheet + 1).PasteSpecial Paste:=xlPasteValues
                    Worksheets("Mean").Range("B" & Sheet + 1).Formula = "=(('SAFO-1'!B80)-('SAFO-1'!B75))"
                    Worksheets("Mean").Range("C" & Sheet + 1).Formula = "=(('SAFO-1'!C80)-('SAFO-1'!C75))"
                    For k = 4 To 41
                        Worksheets("Mean").Cells(Sheet + 1, k).FormulaR1C1 = "=AVERAGE('SAFO-1'!R" & j + 10 & "C" & k & ":R" & j - 9 & "C" & k & ")"
                    Next k
                Else
                j = j + 1
                End If
            Loop
    Next Sheet
    Range("B1:AP2").Copy Worksheets("Mean").Range("A1")
    Worksheets("Mean").Select
End Sub

My idea is to replace 'SAFO-1' by 'Sheet1', to be enventually able to write something like :

Worksheets("Mean").Cells(Sheet + 1, k).FormulaR1C1 = "=AVERAGE('Sheet "& Sheet")'!R" & j + 10 & "C" & k & ":R" & j - 9 & "C" & k & ")"

Thanks in advance!

William Fortin

Answer 1

First, we are going to stop using .Select and instead use object handles. I'm not entirely sure where the name of your sheet comes from but I'm going to assume that it's related to the loop and use that as an example. We get an object handle on the sheet using it's number Set currentSheet = Worksheets(Sheet) and then we can grab it's name and use that where we need to in the formula currentSheet.Name.

I hope that even if this code isn't a complete solution that it shows you how to get where you are going.

Option Explicit

Public Sub Mean()
    
    Dim j As Long
    Dim k As Long
    Dim Sheet As Long
    
    k = 4
    
    For Sheet = 2 To Application.Sheets.Count
        Dim currentSheet As Worksheet
        Set currentSheet = Worksheets(Sheet)
        j = 3
        
        Do
            Dim currentCell As Range
            Set currentCell = currentSheet.Range("A" & j)
            If currentCell.Value = "0" Then
                With Worksheets("Mean")
                    .Range("A" & Sheet + 1).Value = currentSheet.Range("A1").Value
                    .Range("B" & Sheet + 1).Formula = "=(('" & currentSheet.Name & "'!B80)-('" & currentSheet.Name & "'!B75))"
                    .Range("C" & Sheet + 1).Formula = "=(('" & currentSheet.Name & "'!C80)-('" & currentSheet.Name & "'!C75))"
                    For k = 4 To 41
                        .Cells(Sheet + 1, k).FormulaR1C1 = "=AVERAGE('" & currentSheet.Name & "'!R" & j + 10 & "C" & k & ":R" & j - 9 & "C" & k & ")"
                    Next k
                End With
            Else
                j = j + 1
            End If
        Loop While currentCell.Value <> "0"
    Next Sheet
    
    currentSheet.Range("B1:AP2").Copy Worksheets("Mean").Range("A1")
    Worksheets("Mean").Select
End Sub