Formula Update Issues in a Do While Loop Excel 2007

[nglenn82]

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.

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

 

[Blunder1]

not tested but try

activesheet.name = " Q1 - " & x + 1 & ""

 

[nglenn82]

Didn't help. After I run the macro, the issue is that it places the single tick marks around the last number of the sheet name in the formula

=IFERROR(VLOOKUP($C53,Q1 - '1'!$A$34:$M$53,4,FALSE),"")

That is the formula that I am trying to auto update via the above macro. As you can see, the single tick marks are getting placed around the 1, which causes it to not run correctly.

The way I would need the formula to look would be

=IFERROR(VLOOKUP($C53,'Q1 - 1'!$A$34:$M$53,4,FALSE),"")

With the tick marks wrapped around the entire sheet name. My guess is that it has to do with the replace section.

Cells.Replace What:=Sheets(Sheets.Count - 2).Name & "!", Replacement:=Sheets(Sheets.Count - 1).Name & "!"

I'm just not sure though on how to right it so that the tick marks wrap correctly.

 

[Blunder1]

how are you referencing the sheet name in the formula?

 

[nglenn82]

Yes. The Do While Loop creates new sheets that are a copy of a Template. As it creates those sheets, it renames them accordingly; Q1 - 1 (For Quarter 1, Week 1). Each sheet has multiple formulas in it that need to reference the previous sheet. So for instance, with the listed above code, The Q1 - 2 sheet formulas should reference Q1 - 1 and so on.

 

[Blunder1]

I see.It 's because of the naming of the sheet names. Excel is thinking Q1 is a cell ref not a sheet.
For a quick fix, if you make sure that the sheet you copy and the one prior to that have a space in their name then the below will work
Change the find/replace to

Cells.Replace What:="'" & Sheets(Sheets.Count - 2).Name & "'", Replacement:="'" & Sheets(Sheets.Count - 1).Name & "'"

 

[nglenn82]

This helped with the tick marks, but now I am running into another problem of not referenceing the correct Sheet. Now, when it copies, it just repeats the Q1 - 01 sheet without updating to the actual previous sheet. Here is updated macro

Sub CREATE_Q1()
    Dim WKS As Integer
    Dim QTR As Integer
    Dim CurSheet As String
    QTR = 13
    Sheets("NOTES").Visible = True
    Do While WKS < QTR
        WKS = WKS + 1
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Application.WorksheetFunction.VLookup(WKS, Range("$P$2:$R$15"), 2, False)
        Cells.Replace _
        What:=Sheets(Sheets.Count - 2).Name & "!", _
        Replacement:="'" & Application.WorksheetFunction.VLookup(WKS, Range("$P$2:$R$15"), 3, False) & "'!", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Loop
    Sheets("NOTES").Visible = False
    Sheets("Q1 - 01").Select
End Sub

I've also created a small table within the sheet for referencing so that it will now use a vlookup to find the correct sheet names and which sheet it should reference. That table looks like this:

WEEK	SHEETS	REFERENCE
1	Q1 - 01	TEMPLATE
2	Q1 - 02	Q1 - 01
3	Q1 - 03	Q1 - 02
4	Q1 - 04	Q1 - 03
5	Q1 - 05	Q1 - 04
6	Q1 - 06	Q1 - 05
7	Q1 - 07	Q1 - 06
8	Q1 - 08	Q1 - 07
9	Q1 - 09	Q1 - 08
10	Q1 - 10	Q1 - 09
11	Q1 - 11	Q1 - 10
12	Q1 - 12	Q1 - 11
13	Q1 - 13	Q1 - 12

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>