You received better answers, but for interest, you second attempt was headed
in the right direction - you just had a syntax error:

Sub Tester6()
n = 7
Set My_Range = Range(Worksheets("sheet2").Range("a1"), _
Worksheets("sheet2").Range("a1").Offset(n))


MsgBox My_Range.Address(external:=True)
End Sub

works.

--
Regards,
Tom Ogilvy


"pooja " wrote in message
...
This is what I need:
---------------------------
I am working in worksheet 1.I would like to declare a range called
My_Range in worksheet 2.

The cells that make up the range will be known only at run time. ie My
range will have n number of rows where n will be determined when the
program runs.


How do i do this?


So far this is what I've done
-----------------------------------

Set My_Range = Range(Range("a1"), Range("a1").Offset(n))

This works but it selects the range from the current worksheet ie
sheet1.


When I try this:
--------------------

Set My_Range = Range(Worksheets.("sheet2").Range("a1"),
ksheets.("sheet2").Range("a1").Offset(n))

It gives me an error.

Help! I need to finish this asap for an assignment.

Pooja.


---
Message posted from http://www.ExcelForum.com/