Change Part Of Cell Link Using Macro Upon Condition

[CVinje]

I have two workbooks, Book1 and Book2. Book1 links to cells in Book2; however, I need the links in Book1 to be updated as the sheets in Book1 and Book2 progress (a new sheet will be added via another macro for each week of the year). Here is an example of the link (I need the sheet referenced to be updated (the Wk1):<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

[COLOR=black][FONT=Verdana]='[Book2.xls]Wk1'!$K$8<o:p></o:p>[/FONT][/COLOR]

<o></o>
I've tried using the INDIRECT function; however, I'm unable to get it to reference the active sheet; it still looks at sheet Wk1 instead of Wk2, etc. Just in case that would be helpful, here's the INDIRECT function I was attempting to use:<o></o>

[COLOR=black][FONT=Verdana]=INDIRECT("'[Book2.xls]" & MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,99)& "'!K8")<o:p></o:p>[/FONT][/COLOR]

<o></o>
What I'd like to do is incorporate the modification of the links into the macro used to copy the active sheet, rename it incrementally (Wk1, Wk2 , etc), and clear the data from the previous week. My stumbling block is that I want this action to take place only on certain rows of information based of a condition in another cell. For example, this macro places a formula in a cell, only if the cell in column W matches what's in cell D1:<o></o>

[COLOR=black][FONT=Verdana]Sub FillW()[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Dim MyRange As Range, MyText As String[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]MyText = Range("D1").Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]LastRow = Cells(Rows.Count, "W").End(xlUp).Row[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Set MyRange = Range("W8:W" & LastRow)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]For Each c In MyRange[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] If c.Value = MyText Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]     c.Offset(, -11).FormulaR1C1 = "=IF(ISTEXT(RC[-1]),""W"","""")"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Next[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub<o:p></o:p>[/COLOR][/FONT]

<o></o>
Finally, this is the macro to create a new sheet as referenced previously:<o></o>

[COLOR=black][FONT=Verdana] Dim i As Integer, w As Worksheet[/FONT][/COLOR]
[FONT=Verdana][COLOR=black] Application.ScreenUpdating = False[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] ActiveSheet.Copy Before:=Sheets(1)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Set w = ActiveSheet 'the copy[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] On Error Resume Next[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] i = 1[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Do[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]     Worksheets("Wk" & i).Activate[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]     If Err.Number <> 0 Then 'sheet  name doesn't exist yet[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]         w.Name = "Wk" & i[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]         Exit Do[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]     End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]     i = i + 1[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Loop[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] On Error GoTo 0[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] w.Activate[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Application.ScreenUpdating = True<o:p></o:p>[/COLOR][/FONT]

<o></o>
If I could incorporate the modification of the links, based off the criteria in cell D1, by either incrementing automatically like the above macro, or even simply matching the current sheet name; my problem would be solved. By current sheet name, I mean each workbook has sheets with the same name, Wk1, Wk2, etc; I simply want the links to reflect the same on both sheets (if I'm looking at Wk1 in Book1, the links should look to Wk1 on Book2, etc.). <o></o>
<o></o>
Any help would be greatly appreciated <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>
<o></o>
CVinje


<o>Information since I can't attach documents:</o>
<o></o>
<o>Cell containing data to be referenced: D1</o>
<o>Column with data to be compared to cell D1: W</o>
<o>Data starts at Row 8 and continues down (no limit)</o>
<o>Columns with links to be updated: A through R, T, U, and W</o>
<o>If you need to know anything else, or I can email you the workbook, please let me know.</o>
<o></o>