I am trying to get a sheet reference name in a formula updated based on the previous sheet's name. I am trying to get this to work is a Do While Loop. Also within that loop, I am creating a set number of new sheets. Each new sheet should reference the previous sheet. Attached is the macro that I am working with. Currently, when I run this, it creates an error because it places single tick marks (') around the number that appears after the dash in the sheet name (which is the week). Any help that somebody can provide is greatly appreciated. Thanks in advance.
Code:
Sub CREATE_Q1()
Dim X As Integer
Dim I As Integer
I = 13
Do While X < I
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Q1 - " & X + 1
Cells.Replace What:=Sheets(Sheets.Count - 2).Name & "!", Replacement:=Sheets(Sheets.Count - 1).Name & "!", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
X = X + 1
Loop
Sheets("NOTES").Visible = False
Sheets("Q1 - 1").Select
End Sub