change worksheets through code

[Guest]

I have an excel program that uses code to retrieve values from on sheet for
use in a function or sub. The code worked fine but has stoped. This code
snippit Sheets(REPORTSHEET).Activate
sCE = ActiveSheet.Cells(CER, CEC).Value Worked fine but now I find that the
reports sheet is not being activated if the user is on a different sheet when
he calls the sub or function. (CER and CEC are defined as constants to make
it easy to change the layout of a report). One possible problem could be that
part of the program was developed on Office 2003 and part was developed on
Office 2000. I can use all the help I can get on this one it has been driving
me crazy a short trip but not much fun. Thanks Mike

 

[NC]

Mike

is the reportsheet name of the sheet you want to activate then" " are
missing whwn you are calling it
the right code would be sheets("re
portsheet").activate

if you are using reportsheet as sheet index then check the index of
sheet in workbook.

Regards
NC

 

[Ajtb]

I have an excel program that uses code to retrieve values from on sheet for
use in a function or sub. The code worked fine but has stoped. This code
snippit Sheets(REPORTSHEET).Activate
sCE = ActiveSheet.Cells(CER, CEC).Value Worked fine but now I find that the
reports sheet is not being activated if the user is on a different sheet when
he calls the sub or function. (CER and CEC are defined as constants to make
it easy to change the layout of a report). One possible problem could be that
part of the program was developed on Office 2003 and part was developed on
Office 2000. I can use all the help I can get on this one it has been driving
me crazy a short trip but not much fun. Thanks Mike

Hi Mike
If a user is on a different sheet then that is the active sheet.

Instead of sCE = ActiveSheet.Cells(CER, CEC).Value
try
sCE = sheets("ReportSheet").Cells(CER, CEC).Value
then reportsheet doesn't have to be the active sheet.

HTH

Andrew Bourke