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

[shg]

I expect there's some relevant detail missing.

Why not just rename the Totals worksheet to Summary?

[eoghanf17]

Thanks for the reply, it is actually far more complicated with the formulae comprising different combos of well over 20 sheets . the formulae are also Non linear so autofill doesn't work either by row or column. For example Totals!a1 might equal Businessx!c23 + Businessy!dd67 wheres as a2 might equal Businessx!b34 + BusinessY!ae4 so it's a bit of a jigsaw

[eoghanf17]

I just used these worksheet names and cells for simplicity and hopefully then i could append a solution to this example to a larger range

[shg]

Please Login or Register  to view this content.