VBA to Find and Replace in A Dynamic Range

[FrenchCelt]

Hello,

I have a macro that selects the most recent range of data in a table (updated weekly) and then drags it over to the column on the right. The cells have a formula that tell it to find the value in a different sheet, but each week has its own sheet, so when the formulas are dragged over to the new column, it's still referring to the sheet from the previous week. When I do this manually, I just Find and Replace the old week with the new week and click Replace All, but I want the macro to do all this for me. So far I have it to this point:

Sheets("Table").Select
    Range("A2").End(xlToRight).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(59, 0)).Select
    Selection.AutoFill Destination:=Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(59, 1)), Type:=xlFillDefault
    Range("A2").End(xlToRight).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(59, 0)).Select

This selects the sheet with the table, dynamically identifies the most recent cell in Row 2 with data, selects the range of data in that column, drags it over to the next column, and then selects only the range of data in the new column. Now I'm ready to execute the Find and Replace, but I don't know how to code the part where it updates the formula with the new reference.

The basic formula looks like this: ='Wk 33'!$F$3

In this case, I've dragged over values from the Wk 33 sheet. When I do the Find and Replace manually, I'll replace all the Wk 33 values in the range with Wk 34 so it pulls the values from the Wk 34 sheet.

I'd like to either have the VBA automatically change whatever the previously weekly value is to the new value or use relative references (the values in Row 1 are the Wk # value heading each column), whichever is easier to code. So in the latter case, I was trying to figure out how to code having the Find value be whatever is in the current selected range and then offsetting (-1, -1) and the Replace value be whatever is in the current selected range and then offsetting (-1, 0).

For a visual aid, here is a snippet of what I'm talking about:

Wk 33	Wk 34
88%	88%
0%	0%
94%	94%
91%	91%
1%	1%
97%	97%
71%	71%
0%	0%
96%	96%
75%	75%
0%	0%
93%	93%

So right now the values in the two columns are identical until I execute the Find and Replace and turn the Wk 33 values in the Wk 34 column into Wk 34 values.

Does anyone have any suggestions?