I'm working on a project where we have two worksheets. Data is entered
into the first sheet and the second sheet has to reference two cells on
the first sheet and sum them. The problem comes in that every month we
have to use a new set of two sheets and keep the previous two sheets
avalible in the work book for future reference. At this point I have
VBA set up so that when the user clicks on a button on a cover work
sheet it makes copies of the two sheet and names them according to the
name placed in an inputbox and places them after the cover sheet so
that they are sheet 2 and 3 each month. When the sheets are created and
then renamed by the macro, the sheet reference names in the sum formula
on the first sheet don't change and alway point to the old sheets!! How
do I get them to change so that the two sheets that are created every
month are always linked to each other? I've tried using an indirect
formula with a VBA that returns the sheet index position but I can't
get it to work. I've spent literally 10 hours today trying to firgure
this out.

Can any one help! Please before I tear all my hair out! I feel like I'm
missing something really simple but it's total escaping me!

Thanks!