Possible adapt macro to write sum formula (variable sheets) instead of result?

Hi,

I use the macro below (thanks to excelforum!) to get the sum of items (G6) in alleys (variable sheets -> k sheets) of a department (here Fruits & veg, identified in B5 of each alley sheets)

Please Login or Register  to view this content.

It gets me the right total but if someone modifies anything in G6 later on (which has to happen several times), it will not be taken into account in the total Fruits & veg sheet. Therefore, is it possible to get the macro writing an equivalent to =sum(firstk:lastk!G6) instead of the value numsum, knowing that the sheets of each departments would systematically be next to one another? In addition to live adaptations, it would save me many hours thanks to copy-paste formula.

Thanks in advance for any indication

Hi JasperD,

Thank you for your promp response.

With the code I have browsing windows that pop up asking me from which file I should update the values (per alley) and I end up with B5 of all sheets being added, so error value:

In B5 always stand the name of the department (not to be added), in G6 I always have the item solds (to be added).


I have for Fruits and vegetable 3 sheets for 3 alleys numbers (100, 110 and 120) [and dozens of other alley sheets from other departments, so these 3 are selected through their B5]. The result in Fruits & veg sheet G6, should be like =sum(100:120!G6) or =sum(100!G6;110!G6;120!G6).

I changed
form = form & "+" & Split(name, ",")(x) & "!R[-1]C[-5]"

to

form = form & "+" & Split(name, ",")(x) & "!RC"

And I have all sheets G6 added = 0

When I try to add a if
For Each ws In Worksheets
If ws.name <> "Fruits & veg" then
if ws.range("B5") = "Fruits & Veg" then

I have an dismatch error on this line.

Any idea why?