Adjust formula to allow for changing sheet reference.

Hi JB,

Yes I was thinking about a macro, but would some type of loop work replacing each sheet reference with the next variable starting from 1?

Hi again JB,

So far I have come up with this, but it change the '1' to '2' throughout the range B1:H1. What am I missing to make it loop through all the numbers up to 7. I am just testing a small scale right now so therefore up to only 7.

Sub FixFormula()
Application.DisplayAlerts = False
Dim y As Integer
    For y = 2 To 7
        With Sheets(CStr("Sheet1")).Range("B1:H1")
            .Cells.Replace What:="'1'", _
            Replacement:="'" & y & "'", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
        End With
    Next y
Application.DisplayAlerts = True
End Sub