Setting a range when value is given
 

If cell a1 has the value:

b2:b6

How can I assign rng1 to have this value so that I can give a command.

rng1.select
selection.copy

range("z1").pastespecial xlvalues

Is it something like

set rng1 = range("a1").value

or something along these lines?


--
ianripping


------------------------------------------------------------------------
ianripping's Profile: http://www.excelforum.com/member.php...fo&userid=3209
View this thread: http://www.excelforum.com/showthread...hreadid=400731

Setting a range when value is given
 

Put this in a general module:

Sub copy()
Dim rng1 As Range
Dim mycell As String
mycell = Range("A1").Text
Set rng1 = Range(mycell)
rng1.copy Destination:=Range("Z1")
End Sub

Mike F
"ianripping" wrote
in message ...

If cell a1 has the value:

b2:b6

How can I assign rng1 to have this value so that I can give a command.

rng1.select
selection.copy

range("z1").pastespecial xlvalues

Is it something like

set rng1 = range("a1").value

or something along these lines?


--
ianripping


------------------------------------------------------------------------
ianripping's Profile:
http://www.excelforum.com/member.php...fo&userid=3209
View this thread: http://www.excelforum.com/showthread...hreadid=400731

Setting a range when value is given
 

Ian,

I've added a qualifier here for the sheet too:

Sub SelectiveCopyValue
Dim rngCopy as Range
With Sheets(1)
set rngCopy = .Range(.Cells(1,1).Value)
'OR set rngCopy = .Range(.Range("a1").Value)
rngCopy.Copy
.Range("Z1").PasteSpecial xlValues
End With

Robin Hammond
www.enhanceddatasystems.com

"ianripping" wrote
in message ...

If cell a1 has the value:

b2:b6

How can I assign rng1 to have this value so that I can give a command.

rng1.select
selection.copy

range("z1").pastespecial xlvalues

Is it something like

set rng1 = range("a1").value

or something along these lines?


--
ianripping


------------------------------------------------------------------------
ianripping's Profile:
http://www.excelforum.com/member.php...fo&userid=3209
View this thread: http://www.excelforum.com/showthread...hreadid=400731

Setting a range when value is given
 

Thanks this is grea

--
ianrippin

-----------------------------------------------------------------------
ianripping's Profile: http://www.excelforum.com/member.php...nfo&userid=320
View this thread: http://www.excelforum.com/showthread.php?threadid=40073