VBA code for changing a cell source

Hi there,

I am busy working on a dynamic workbook that relies on copying from pages that are created by pushing buttons etc.... so the documents are not in existence until their buttons are pushed.

I am now creating a new sheet, which is copied from an existing sheet, but needs to be modified to draw it's detail from one of the other new sheets created ( so not one in existence ).

To explain it basically -
The relevant cell ( D123 ) in the copied document has a value of "'MAIN ESTIMATE'!L131" and I would change the one cell to the below VBA code to adjust the "source" of the information.
Range("D123").Value = "='WEATHER DAY'!L131"

However, the entire document has to pull detail from the Weather Day sheet instead of the Main Estimate and I would be here for a week writing a single line of code for every cell that needs to be changed.

So I am wondering if it is possible to do a Range change over a multitude of cells and only replace 'Main Estimate' and not the Cell detail.

If I was to do it in the normal excel it would be the "replace" function... is there a VBA code to "replace" values?

Thanks so much for your help!! Hope my ramblings make sense.

Toni