Thanks for all the help, and especially JE McGimpsey because his way
worked.

However afterwards I needed to use the codes to create a formula which
is sums up the first 5 sheets' (infront of the current sheet) cell
'D4'. and have that total in the current sheets cell D4. However it
doesnt work =(

You must think, why dont i just click on each cell whilst creating the
formula, well i couldnt because this macro needs to run everyweek, and
if i selected those cells, it will repeat totalling those specific
cells and not the ones its suppose to.

Pls help

thx in advance
Bonbon


--
Bonbon
------------------------------------------------------------------------
Bonbon's Profile: http://www.excelforum.com/member.php...o&userid=31866
View this thread: http://www.excelforum.com/showthread...hreadid=515940