Reference Cells on another worksheet
 

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!

Reference Cells on another worksheet
 

How is the code refering to the sheets?
sheet2 or sheets("Name") ?
sheets("Name").select
will always reference the sheet with that designated name.

--
Regards

Corey

"Mike" wrote in message
ups.com...
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!

Reference Cells on another worksheet
 

Hi thanks for the help,

The firmula I use to reference is =SUM('Master Sample Data
Form'!E7,'Master Sample Data Form'!H7) the problem is I need to be able
to have some way to change the sheet name 'Master Sample Data' to the
name supplied in the Input box. So when the code creates a new sheet
for the month called November Data the formual then refers to the
November data sheet. I can't figure out how to use a variable to set
the sheet name!

Thanks!

Corey wrote:
How is the code refering to the sheets?
sheet2 or sheets("Name") ?
sheets("Name").select
will always reference the sheet with that designated name.

--
Regards

Corey

"Mike" wrote in message
ups.com...
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!