Hi

Use dynamic ranges, like
MyRange1=OFFSET(Sheet1!$M$4,1,,COUNTA(Sheet1!$B:$B )-1,10)
MyRange2=OFFSET(Sheet2!$M$4,1,,COUNTA(Sheet2!$B:$B )-1,10)

In this example the column B is a 'key column', where none of cells in
current datarange (with your data B4:B12 on Sheet1 and B4:B45 on Seet2) is
empty, and where all cells outside of current datarange are empty. When
there is not such column in your table, you have to create some helper
column where these conditions are met, and use this column in COUNTA part of
formula.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



wrote in message
...
Hi All,

I have a some data in a sheet from which I copy selected rows into new
sheets when specific criteria is met.

Now I would like add names to ranges that are present in these new
sheets The number of rows copied vary. How can I write a vba code that
can capture this?

Ex (in sheet 1 i have data starting from b4 to w12. In this I want to
name a range which starts from M5 to W12....
In sheet 2 I have a data which starts from b4 to w45. in this sheet I
want to name a range which starts from M5 to W45)

Hope my explanation is clear..
Any Suggestions

Thanks for your help
vishnu