Second Main Code. Sub FoxyMultiCellNamedRanges()
Range referencing In Excel and VBA

Code section up to and including Rem 3 take us as far as the last code

Before going onto the rest of the code, as an aside, a review of some basic techniques for bringing a range of data values into a main workbook, such as our "MasturFile.xlsm" , from a closed data workbook, " Data1.xls"
This post is not really to do with named ranges, but we must understand some advanced techniques relating to applying the ideas discussed so far to the multicell ranges, before we can do similar data retrieval from multicell data ranges.

"Conventional formulas and range references"
Excel has two basic ways to Hold a single cell reference. Either
it holds it as a fixed co ordinate,
or
it holds it as a fixed vector. The vector corresponds, ( by default settings, if you don't specify otherwise ), as a fixed angle and direction from the cell to the worksheet origin.
One way in which Excel can be told which system to use by including a $ sign if we want to use the co ordinate system. If no $ is included then Excel holds the fixed vector.
The fixed vector will mean that if a cell reference in a particular cell , such as =B6 , is copied to the next cell to the right, the fixed vector is copied and shifted one place to the right. The fixed vector is responsible for bringing the value from B6 into the original cell. That same vector if placed in the next cell to the right will bring in the value of B7. Excel will then display correspondingly the appropriate reference. This would be either =B7 or $B$7 , but Excel conventionally keeps the convention given to it, so it will display =B7
In VBA things work similarly. If I fill in a single cell with the reference =B6 using the , ( simplified) code line of .._
__ Range("B2").Value = "=B6"
____ _.. then the vector is placed in cell B2, so I get

C6.jpg : https://imgur.com/RrR2zrA
Row\Col
A
B
C
D
1
2
=C6
3
4


Paste a Single cell reference( in fixed vector format) across a multicell range
If I put the same reference, =C6 , across a range, whether manually by
copy / paste, or draging
or
by VBA thus: .. _
__ __ Range("B2:C3").Value = "=B6"
____ _.. then the same fixed vector is copied thus:
C6inB2toC3.JPG : https://imgur.com/BrMGrqn

The convention remains to stay in the fixed vector notation, so the reference put in the cells is:
Row\Col
A
B
C
D
E
1
2
 =C6 =D6
3
 =C7 =D7
4
5
6
 a b
7
 c d
8

In the spreadsheet we will see:
Row\Col
A
B
C
D
E
1
2
 a b
3
 c d
4
5
6
 a b
7
 c d
8


Coming back to our last formula in the last post, we had this:
='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10
That brought in our "Suppliment" heading from here …_
Row\Col
A
B
C
D
9
10
 Suppliment Kcal
11
 BCAA
398
12
 EAA
400
13
Worksheet: Tabelle1
_..... into out main file , using a code line of this form, (simplified)

See that full code line in the last code, Sub FoxySingleCellNamedRanges() , code line 880 , was of this form, simplified, .._
Range("B10").Value = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10"
____ _..which gave the formula of this form in B10 of the main File: ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10


In the second code , Sub FoxySingleCellNamedRanges() we use the similar code line ( at '3b) "Fixed vector" B11 into main workbook at B11 ) like
Range("B11").Value = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11"
____ _..which gives the formula of this form in B11 of the main File: ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11
This results in this in the main file, that is to say, we bring in one of the data values, "BCAA", from data file "Data2.xlsx"
MasturFile.xlsm
Row\Col
A
B
C
D
1
2
3
4
 Nutrition Energy
5
 Food
6
7
8
9
10
 Suppliment
11
 BCAA
12
13
14


Following the arguments of this post so far, we see that we must only change the left hand side of that formula, as in code line '3c) [size=1"Fixed vector" B11 into main workbook into B11 C11 B12 and C12 [/size] to this form : .._
Range("B11:C12").Value = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11"
____ _.. and then we will have formulas of this form in the master file,
Row\Col
A
B
C
D
11
 ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11
='C:\MyFolder \[Data2.xlsx]Tabelle1'!C11
12
 ='C:\MyFolder \[Data2.xlsx]Tabelle1'!B12
='C:\MyFolder \[Data2.xlsx]Tabelle1'!C12
13


Correspondingly, we see in the master file all the data from Data 2 file:
Row\Col
A
B
C
D
11
 BCAA
398
12
 EAA
400
13


_._______________

Paste a Mullticell reference
The basic idea discussed so far is OK, but there is a subtle problem for a multicell range paste.
This would be the case, if for example our named range was referring to the data 2 range of B11:C12. If we wish to use a named range reference , we cannot use the single fixed vector reference. We are dealing with multicells.
We need to consider some advanced theory or how Excel actually works, the end result of which is an understanding of the so called CSE stuff..
We will consider this in the next post.

_.____




Refs for this post
http://www.eileenslounge.com/viewtop...=31150#p241197
https://teylyn.com/2017/03/21/dollarsigns/#comment-191