Named Range / use of Indirect?

[jim may]

In cell A1 I have the text Testme

Testme is a Named Range
with RefersTo set to:
=Sheet1!$D$3:$D$5

In VBA - How do I

Range("A1").copy Range("D4").PasteSpecial xlPasteValues

And D4:D6 will show
123
456
789

This thing is beating me up -- Indirect is likely involved, but,,,
TIA

 

[Yard]

Do you mean

Range("A1").copy
Range("Testme").Cells(2,1).PasteSpecial xlPasteValues

 

[jim may]

Thanks Yard, but the Value in A1 is CHANGABLE (a Variable, but always a registered Named Range)

 

[jim may]

In VBA - How do I
Correction - My first note should have been:

Range("A1").copy Range("K4").PasteSpecial xlPasteValues

And K4:K6 will show
123
456
789

Sorry for My Confusion!!

 

[Jon von der Heyden]

Hi Jim

I'm confused

You have said that A1 contains Testme, yet when you copy and paste; it pastes 123?

If you want to use the value in A1 to direct you to a named range in VBA (as a paste destination), then perhaps:

Range(Range("A1").Value).PasteSpecial xlValues

 

[Yard]

Totally lost now. Why would K4:K6 equal

123
456
789

??

 

[Jon von der Heyden]

I think I understand.

A1 has text Testme

Tesme range refers to D3:D5 and it is populated with:
123
456
789

And you want to copy this (based on named range name entered in A1) to K4.

Right?

Range(Range("A1").Value).Copy
         Range("K4").PasteSpecial xlValues

 

[jim may]

Yard, Thanks
Answer?
because Sheet1!$D$3:$D$5 contains
123
456
789

 

[jim may]

Jon - YOU WERE RIGHT ON !!
Thanks,
I need to study this to better understand why
I couldn't do this.
Jim

 

[Yard]

I think (hope) Jon's got it, because otherwise I'm even more confused.