Range name is in validation list

[John Wood]

This problem CAN'T be this difficult.
I have two named ranges, period1 and period2, that are in a validation list. I can select either value - no problem. My problem is I can't seem to get that value into a range in order copy the corresponding cells. Here's what I have so far:

Private Sub CommandButton1_Click()
Dim source As Range
Set source = Range("A32") 'this is the cell with the validation list. It only contains the list "period1" and "period2"
source.Copy
Range("A40").Select
Selection.PasteSpecial
End Sub

All that happens is a value of "0" appears in cell A40. If I replace Range("A32") with Range("period1") it works perfectly.

I'd appreciate any help with this. I know the solution is probably very simple but sometimes I get tripped up on the obvious!

John

 

[xld]

Are you on the correct sheet when you use A32?

 

[John Wood]

Yes. I only have one worksheet in the workbook. I'm just having trouble getting the value of what's in the validation cell into the formula so that it copies and pastes the correct data range.

 

[jim may]

Dim source As Range
Set source = Range("A32") ' or Set source = Range("period1")
source.Copy
Range("A40").PasteSpecial xlPasteValues ' this additional argument needed

 

[John Wood]

I'm doing a bad job explaining this and I do apologize.

The cell A32 contains my validation list, which can only be period1 or period2. These are both named ranges. If I use Range ("A32"), just as it is, it will paste the contents of the cell into A40. So if I've selected "period2" from my list it simply pastes the word "period2" into A40. What I want it to do is recognize "period2" as a range and then paste that range of cells. If I manually change the Range ("A32") to Range ("period2") it works perfectly but then having a validation list is rather redundant and I need this process to be rather fool proof for others to use.

I hope this clears things up a bit.

Again, your help is much appreciated.

 

[jim may]

John, I didn't purposely abondon you on this -- It's just that I didn't have the
answer, UNTIL NOW !!! Hooray..... Have a great weekend
Jim here in WARM Virginia (70 degrees Staurdau and Sunday)

Sub Test2()
Dim source As Range
Set source = Range(Range("A32"))
Range(Range("A32").Value).Copy
Range("A40").PasteSpecial xlValues

Application.CutCopyMode = xlCopy
End Sub

 

[John Wood]

A huge thank you for your help. It works perfectly.