Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Referencing a Range of Cells
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 wrote in message ... 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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting empty cells within a range of cells | New Users to Excel | |||
automatically fill in a range of cells | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions |