Cell value picking

[daniel chen]

Option Explicit
Sub value_picking()
' I use this script to pick value anywhere within the sheet, _
and paste it in cell A1.
Dim reg1 As Variant
reg1 = ActiveCell.Value
Range("A1") = reg1
End Sub

'Similarly, I want to select a range of varrious size, _
and paste its value in Range("A1:??"), _
so long they are not overlapped.
'Please help.

 

[Jim Cone]

Daniel,

I think this does what you want...
'--------------------------------
Sub TransferSelectionValues()
' Jim Cone - 10/27/2004
Dim rngSelect As Excel.Range
Dim rngA1 As Excel.Range

Set rngSelect = Selection
Set rngA1 = Range("A1").Resize(rngSelect.Rows.Count, rngSelect.Columns.Count)

' If they don't overlap then transfer values...
If Application.Intersect(rngSelect, rngA1) Is Nothing Then
rngA1.Value = rngSelect.Value
Else
MsgBox "Ranges overlap ", vbExclamation, " Blame Daniel Chen"
End If

Set rngA1 = Nothing
Set rngSelect = Nothing
End Sub
'--------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[daniel chen]

Hi, Jim,
You are great! Exactly what I wanted. Thank you very much.