Referencing a Range of Cells

[Derek C]

Hi all,

I have named a range of cells on one worksheet, say I called (A1:B10) as
"My_Range" on "WorkSheet_1".

And then I got another worksheet which want to copy the same value on
another worksheet "WorkSheet_2" using the name as a reference. How could I do
this?

Also, could I specify a particular value inside the named range? For example
how could I display the B3 cell in "My_Range"?

Thanks for your help.

Derek C

[FSt1]

hi.
not sure why you would want to do it this way but try this

Sub macSetRange()
Dim rng As Range 'range to copy
Dim rng1 As Range 'copy to range
Dim rcnt As Long 'row counter
Dim ccnt As Long 'column counter
ActiveWorkbook.Names.Add Name:="rngB", RefersToR1C1:="=Sheet2!R1C1:R11C2"
Application.Goto reference:="rngB"
Set rng = Selection
rng.Select
rcnt = rng.Rows.Count - 1
ccnt = rng.Columns.Count - 1
Sheet3.Select
Set rng1 = Range(Range("A1"), Range("A1").Offset(rcnt, ccnt))
rng1.Select
rng1.Value = rng.Value
sheets("sheet2").select
msgbox("The contens of cell B3 is" & Range("B2").value
end sub

regards
FSt1
"Derek C" wrote:

> Hi all,
>
> I have named a range of cells on one worksheet, say I called (A1:B10) as
> "My_Range" on "WorkSheet_1".
>
> And then I got another worksheet which want to copy the same value on
> another worksheet "WorkSheet_2" using the name as a reference. How could I do
> this?
>
> Also, could I specify a particular value inside the named range? For example
> how could I display the B3 cell in "My_Range"?
>
> Thanks for your help.
>
> Derek C

[Bernie Deitrick]

Derek,

To copy My_Range to the same location:
Range("My_Range").Copy Worksheets("WorkSheet_2").Range(Range("My_Range").Cells(1,1).Address)

To copy My_Range to a specific location:
Range("My_Range").Copy Worksheets("WorkSheet_2").Range("C10")


Either of these two will display the value in the 3 row and second column of the named range, no
matter where the named range is located on the sheet:
MsgBox Range("My_Range").Cells(3,2).Value
MsgBox Range("My_Range").Range("B3").Value

If the named range always starts in cell A1, then this is equivalent:
MsgBox Range("B3").Value


HTH,
Bernie
MS Excel MVP


"Derek C" <Derek [email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have named a range of cells on one worksheet, say I called (A1:B10) as
> "My_Range" on "WorkSheet_1".
>
> And then I got another worksheet which want to copy the same value on
> another worksheet "WorkSheet_2" using the name as a reference. How could I do
> this?
>
> Also, could I specify a particular value inside the named range? For example
> how could I display the B3 cell in "My_Range"?
>
> Thanks for your help.
>
> Derek C

[Derek C]

Thanks guys!

I wanna do this because I have to reference a lot of range of cells. and the
source i am referencing from is done by my colleague. sadly he changes his
table very often (not just the values, but also the style of table, the
location of each value etc. basically is like a new table everyday). I would
like go back to the office everyday, name the range of cells which i needed,
and my table will update automatically.

is there a better way to do this?

Also would you mind telling me where to put the code? thanks a lot

"FSt1" wrote:

> hi.
> not sure why you would want to do it this way but try this
>
> Sub macSetRange()
> Dim rng As Range 'range to copy
> Dim rng1 As Range 'copy to range
> Dim rcnt As Long 'row counter
> Dim ccnt As Long 'column counter
> ActiveWorkbook.Names.Add Name:="rngB", RefersToR1C1:="=Sheet2!R1C1:R11C2"
> Application.Goto reference:="rngB"
> Set rng = Selection
> rng.Select
> rcnt = rng.Rows.Count - 1
> ccnt = rng.Columns.Count - 1
> Sheet3.Select
> Set rng1 = Range(Range("A1"), Range("A1").Offset(rcnt, ccnt))
> rng1.Select
> rng1.Value = rng.Value
> sheets("sheet2").select
> msgbox("The contens of cell B3 is" & Range("B2").value
> end sub
>
> regards
> FSt1
> "Derek C" wrote:
>
> > Hi all,
> >
> > I have named a range of cells on one worksheet, say I called (A1:B10) as
> > "My_Range" on "WorkSheet_1".
> >
> > And then I got another worksheet which want to copy the same value on
> > another worksheet "WorkSheet_2" using the name as a reference. How could I do
> > this?
> >
> > Also, could I specify a particular value inside the named range? For example
> > how could I display the B3 cell in "My_Range"?
> >
> > Thanks for your help.
> >
> > Derek C