VBA help reducing chain of linked cells

[eoghanf17]

To simplify the issue: I have 4 worksheets in my workbook: "Business X", "Business Y", "Totals" and "Summary".
The formula in cell A1 of "Totals" is:
='Business X'!A1+'Business Y'!A1
and the formula in cells A1 of "Summary" is
=Totals!A1
I want to cut out the middle man ("Totals" worksheet) essentially and build a macro that goes to the cell referred to in A1 of the "Summary" tab, copies that formula and then pastes it into back into cell A1 of "Summary" tab, hence eliminating the need for "Result" tab.
So essentially Summary!a1 = totals!a1= BusinessX!a1 + BusinessY!a1
condenses to: Summary!a1 = BusinessX!a1 + BusinessY!a1
Of course it is easily done in the example provided but i'd hope to apply it to work on a grand scale i.e. all of the cells in a selected range in the "Summary" tab
Thanks in advance for any assistance

 

[JackDanIce]

What is the range that is selected in Summary?
Are all the formula's linear like your example against Business X and Business Y, e.g. each cell is the sum of the same respective cells of Business X and Business Y?

 

[JackDanIce]

Also, can you clarify:

To simplify the issue: I have 4 worksheets in my workbook: "Business X", "Business Y", "Totals" and "Summary".

vs

hence eliminating the need for "Result" tab.

Is it 5 sheets with Result tab or not?

So essentially Summary!a1 = totals!a1= BusinessX!a1 + BusinessY!a1
condenses to: Summary!a1 = BusinessX!a1 + BusinessY!a1

Suggests it's the Totals tab you are eliminated the need for.

 

[eoghanf17]

Hi thanks a million for the reply, unfortunately the sheets aren't linear it's like a jigsaw so autofilling of A1 won't work either filling down through rows or even across columns, i just used A1 in each sheet to make the example as simplistic as possible. Also apologies There is no such "Result" tab, I meant to say Summary, so to confirm, there is only 4 sheets. Thanks

 

[JackDanIce]

Can you give further examples then please.

Difficult to suggest code when there isn't an easily identify-able pattern.

I don't think this will suit all situations but can you use Find+Replace to remove all instances of Totals! from the formula?

What if you rename Summary to Summary_Orig and Totals to Summary, save that workbook with a different name, then delete Summary_Orig?

 

[shg]

Cross-posted at https://www.excelforum.com/excel-pr...-vba-help-reducing-chain-of-linked-cells.html