Hi,

I have a problem in which I have to copy the values in a range of
cells ( this range is dynamic and decided at run time) from worksheet
1 to worksheet 3. Then the control must return to worksheet 1.


Any suggestions on how to do this would be highly appreciated.

Thanks and Happy New Year

Rohith Kori

more info needed. You do not need to goto the destination sheet. The basic
idea is
[destinationsheet!a1].value=[sourcesheet!a1].value

--
Don Guillett
SalesAid Software

"Rohith Kori" wrote in message
om...
Hi,

I have a problem in which I have to copy the values in a range of
cells ( this range is dynamic and decided at run time) from worksheet
1 to worksheet 3. Then the control must return to worksheet 1.


Any suggestions on how to do this would be highly appreciated.

Thanks and Happy New Year

Rohith Kori

Hi Don,

Sorry for not giving the complete details.

I have a range of values B23:O36 on sheet 1 which I need to copy into
Sheet 3.

The problem is that the range on sheet 1 has certain validations ( only
numbers allowed ) and formatting ( color change on entering data )
associated with it. So when I try the

worksheets("sheet1").range("b23:O36").copy _
destination:=worksheets("sheet3").range("b23:O36")

it gives an error, subscript out of range.

When I run the same code for cells which do not have any formatting it
works.

Any suggestions on how to overcome this problem.

Thanks
Rohith





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Are you sure your activeworkbook is the one you think it is?

Typically, subscript out of range errors are caused by not having the names of
sheets that you think you have. (Check for leading/trailing spaces if you're
positive you're running the code against the correct workbook.)

Your code worked ok for me when I had data|validation and format|conditional
formatting in the range to be copied.



Rohith Kori wrote:

Hi Don,

Sorry for not giving the complete details.

I have a range of values B23:O36 on sheet 1 which I need to copy into
Sheet 3.

The problem is that the range on sheet 1 has certain validations ( only
numbers allowed ) and formatting ( color change on entering data )
associated with it. So when I try the

worksheets("sheet1").range("b23:O36").copy _
destination:=worksheets("sheet3").range("b23:O36")

it gives an error, subscript out of range.

When I run the same code for cells which do not have any formatting it
works.

Any suggestions on how to overcome this problem.

Thanks
Rohith

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson