ExcelBanter - Named ranges and variables

I guess I am having trouble following it all - if you could show more of the
code it might help. There are a few things I don't quite get. When you say
"The variable Goal now contains the value 'Monkey' instead of MyRange" it is
ambiguous: if Goal is a Range variable it contains many properties - the
..Value property (which is also the default property if you were to do, say,
MsgBox Goal or debug.print Goal) will be the cell contents, so it would make
sense for that to be 'Monkey' since that is what the range Goal will now
contain. So is it the value of Goal you are checking, or the Name associated
with Goal? That is why it would be helpful to see more of the code: I would
need to see how you define Goal (the Dim statement), the exact code where you
set it to be equal to MyRange, and then where it is that you are saying the
value of Goal is now 'Monkey' and not 'MyRange'.

But I made a spreadsheet with a sheet named Docs and a range named 'MyRange'
on another sheet, then made another range (3 cells on Docs, A1:A3) and named
it InfoLine, then ran this sub:

Public Sub TEST()

Dim Goal As Range
Set Goal = Range("MyRange")

Worksheets("Docs").Range("InfoLine").Copy
With Goal
.Select
.PasteSpecial (xlValues)
.PasteSpecial (xlFormats)
End With

Debug.Print Goal.Value
Debug.Print Goal.Address
Debug.Print Goal.Name

End Sub

The result was:
Monkey
$M$20
=Sheet2!$M$20

As expected, M20, N20, and O20 contained the values from InfoLine, with
'Monkey' in M20; and there was no range named "Monkey" in my workbook
(verified: ctrl-G for Goto, showed the named ranges and no 'Monkey' in list).
This is in Excel XP.

So if there is something I am not understanding, please post more of the
code so I can check it out.
--
- K Dales

"Mats Samson" wrote:

Hi,
no, I don't agree!
In fact the first originating cell had an entire different Name and you are
right that this name isn't pasted to a new location. I also agree that by
cutting and pasting also the name moves together with the paste. But my story
is different.
In my case I only Copied a range and pasted it to another location.
The Start-location-first cell (B1) contains the Value/String 'Monkey'
The goal-location-first-cell (M20) had the Name 'myRange' and this reference
was declared in the variable 'Goal' via Goal=myRange. So I copied o couple of
cells ('InfoLine') and used Goal.Select to move to 'myRange' at M20 with
following code:
Worksheets("Docs").Range("InfoLine").Copy
With Goal
.Select
.PasteSpecial (xlValues)
.PasteSpecial (xlFormats)
End With
The Value, not the Name, of the first pasted cell is 'Monkey'. PasteSpecial
(xlValues) will paste 'Monkey' as a value/string in M20, but also define
'Monkey' as a Name with the reference M20. Furthermore, from now on the
variable 'Goal' contains the value 'Monkey' instead og 'myRange'. Checking
in Insert/Name/Define both 'myRange' and 'Monkey' exists and refers to the
same cell. At the Name-bar
'Monkey' is shown when activating cell M20.
It looks strange to me and has impact if you want to use 'Goal' for
'myRange' and name another cell to 'myRange' later in the code.
Regards
Mats

"K Dales" wrote:

PasteSpecial xlPasteValues will not in itself paste the name of the
originating cell into the destination cell. That is, if you have copied the
first cell. If you cut it, the original cell moves to the "paste" cell and
brings its name with it. This behavior is no different than if you cut and
paste manually in Excel. So you can control how it behaves depending on if,
before pasting, you have cut or copied the first cell.

Hope this makes sense!
--
- K Dales

"Mats Samson" wrote:

Hello everybody,
trying to solve my problem with using variables referring to named ranges
(see the same header as bove from today) I discovered a what I believe is a
fault or maybe I'm overlooking something!
I have a cell named 'myRange' at M20 and want the code to navigate to this
cell and paste information copied elsewhere. If I use myRange.Select
Pastespecial (xlvalues) (the originating cells contains formulas) the pasting
will define the value of the first cell (f.i. 'Monkey') as a Name
referencing to cell M20 (cell M20 contains the pasted value/string 'Monkey'
too). This new Name overrides 'myRange' in the Name-bar even though both
Names (Monkey and myRange) exists, referencing to the same cell.
Who is the monkey, Excel or me?
Best regards
Mats