Assign Ranges On Different Worksheets.

[Offace]

Hello MS Excel Newsgroup,

I have constructed (well almost) an application using Microsoft Excel 2007 SP2,
VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store
12 (small) matrices of numbers, each matrix encapsulated in it's own named Range.
The other Worksheet contains user controls which the user can select a few
predefined values that, kick various VBA routines off, and display the results on
a Graph, on the user's Worksheet (and a few other results).

The user would like to see the underlying matrix on their Worksheet. Given the
parameters the user chooses, I can set a named Range on the user's Worksheet to
the exact dimensions of it's data matrix, on the other Worksheet.

Learning that;

UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData")

- nor -

UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value

- nor -

UserSheet.Range("DataDump").AnythingElsePertainingToValueOrObject =
MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject

work, is there any 'innate' functionality in Excel that permits this?

Is looping or Array assignment to the range the better way of approaching it?

 

[Mike H]

Hi,

I can't get my head around your names but the syntax is like this

Sheets("Sheet1").Range("myrange").Copy
Destination:=Sheets("Sheet2").Range("A1")

This copies the named range called "myrange" from sheet 1 to a1 of sheet 2

Mike

 

[Patrick Molloy]

if your eranges are exactly the same size, then use the INDIRECT() function

so on your main sheet type a range name in say A1
elasewhere select an area of the correct size and erray enter {=INDIRECT(A1) }

 

[Offace]

Thank you very much Mike, works well, and not only that, your response also made
me see the stupidity of my ways in the form of an obvious syntax error. Now my
post works too.

See what happens when you look at something (that's incorrect) for to long????

Thanks again Mike.

| Hi,
|
| I can't get my head around your names but the syntax is like this
|
| Sheets("Sheet1").Range("myrange").Copy
| Destination:=Sheets("Sheet2").Range("A1")
|
| This copies the named range called "myrange" from sheet 1 to a1 of sheet 2
|
| Mike
|
| "Offace" wrote:
|
| >
| > Hello MS Excel Newsgroup,
| >
| > I have constructed (well almost) an application using Microsoft Excel 2007
SP2,
| > VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to
store
| > 12 (small) matrices of numbers, each matrix encapsulated in it's own named
Range.
| > The other Worksheet contains user controls which the user can select a few
| > predefined values that, kick various VBA routines off, and display the results
on
| > a Graph, on the user's Worksheet (and a few other results).
| >
| > The user would like to see the underlying matrix on their Worksheet. Given
the
| > parameters the user chooses, I can set a named Range on the user's Worksheet
to
| > the exact dimensions of it's data matrix, on the other Worksheet.
| >
| > Learning that;
| >
| > UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData")
| >
| > - nor -
| >
| > UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value
| >
| > - nor -
| >
| > UserSheet.Range("DataDump").AnythingElsePertainingToValueOrObject =
| > MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject
| >
| > work, is there any 'innate' functionality in Excel that permits this?
| >
| > Is looping or Array assignment to the range the better way of approaching it?
| >
| >
| > --
| >
| > Thanks and regards,
| >
| > Offace
| >
| >
| >
| >

 

[Mike H]

Glad I could help and thanks for the feedback

 

[Offace]

Wow...! That was not at all apparent from the Microsoft Excel Inbuilt Help, type
"excel indirect function" into Google and it really opens that function up. It is
actually really good for "on the fly" data range work. Thanks Patrick. Never
occurred to me what that function was all about, never looked at it, thought it
was vector geometry related by the name of it. Thanks again.

|
| if your eranges are exactly the same size, then use the INDIRECT() function
|
| so on your main sheet type a range name in say A1
| elasewhere select an area of the correct size and erray enter {=INDIRECT(A1) }
|
|
|
| "Mike H" wrote:
|
| > Hi,
| >
| > I can't get my head around your names but the syntax is like this
| >
| > Sheets("Sheet1").Range("myrange").Copy
| > Destination:=Sheets("Sheet2").Range("A1")
| >
| > This copies the named range called "myrange" from sheet 1 to a1 of sheet 2
| >
| > Mike
| >
| > "Offace" wrote:
| >
| > >
| > > Hello MS Excel Newsgroup,
| > >
| > > I have constructed (well almost) an application using Microsoft Excel 2007
SP2,
| > > VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to
store
| > > 12 (small) matrices of numbers, each matrix encapsulated in it's own named
Range.
| > > The other Worksheet contains user controls which the user can select a few
| > > predefined values that, kick various VBA routines off, and display the
results on
| > > a Graph, on the user's Worksheet (and a few other results).
| > >
| > > The user would like to see the underlying matrix on their Worksheet. Given
the
| > > parameters the user chooses, I can set a named Range on the user's Worksheet
to
| > > the exact dimensions of it's data matrix, on the other Worksheet.
| > >
| > > Learning that;
| > >
| > > UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData")
| > >
| > > - nor -
| > >
| > > UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value
| > >
| > > - nor -
| > >
| > > UserSheet.Range("DataDump").AnythingElsePertainingToValueOrObject =
| > > MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject
| > >
| > > work, is there any 'innate' functionality in Excel that permits this?
| > >
| > > Is looping or Array assignment to the range the better way of approaching
it?
| > >
| > >
| > > --
| > >
| > > Thanks and regards,
| > >
| > > Offace
| > >
| > >
| > >
| > >