VBA: Copy Paste Range Question

[Ryan Saari]

Quite a simple question:

I would like to be able to type a range name, all of which I have already defined, into cell A1. When typed in, I would like VBA to copy that range to cell F3however big the range is). It would also be nice for it to automatically do this whenever A1 is changed. Take a look at the setup:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>test1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>(RANGE1) name:test1</TD><TD>apple</TD><TD>orange</TD><TD>apple</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD>orange</TD><TD>apple</TD><TD>orange</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD>apple</TD><TD>orange</TD><TD>apple</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>(RANGE2) name:test2</TD><TD>grape</TD><TD>lime</TD><TD>grape</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD>lime</TD><TD>grape</TD><TD>lime</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD>grape</TD><TD>lime</TD><TD>grape</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

The VBA that I have come up with will only copy "test1" to cell F1 and that's it. Please help!

 

[ajm]

Ryan,

Check out Indirect in the VBA help menu. and then just modify your copy paste macro accordingly.

ajm

 

[Ryan Saari]

I couldn't find anything on indirect in the help. I think I had tried that before. But you comment gave me a great idea! The Bolded text below will grab the named range if anyone is looking for a solution.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Range(Range("A1")).CurrentRegion.Copy Range("F3")
End If
End Sub

FINALLY! A solution! YAY.