Select cells for copy on the fly

I'd recommend creating a form for selecting data. They can be very simple--consisting of only a RedEdit box, which lets the user select a range, and an OK command button. You can load the form in your macro with these lines, assuming your form's name is Form1:

Load Form1
Form1.Show

If the RefEdit box is called RefEdit1 and the OK button is called CommandButton1, use this code to select the range in the RedEdit box when the user hits OK:

Private Sub CommandButton1_Click()
Range(RefEdit1.Value).Select
Unload Form1
End Sub

After that code is executed, Excel will go back to your main macro that you loaded the form from.

-Ben

As an alternative to Bens suggestion you could try
this;

Sub CopyFrom_To()
Dim Cpy 'Rg to copy
Dim Dest 'Destination Rg

On Error Resume Next
Set Cpy = Application.InputBox("Select range to copy", Type:=8)
On Error GoTo 0
If IsEmpty(Cpy) Then End
Cpy = Cpy.Address

On Error Resume Next
Set Dest = Application.InputBox("Select range to Paste to:", Type:=8)
On Error GoTo 0
If IsEmpty(Dest) Then End

Dest = Dest.Address(external:=True)

ActiveSheet.Range(Cpy).Copy Destination:=Range(Dest) 'Range("B11")
Set Cpy = Nothing
Set Dest = Nothing

End Sub

Ivan

Modified version due to error

Sub CopyFrom_To()
Dim Cpy 'Rg to copy
Dim Dest 'Destination Rg

On Error Resume Next
Set Cpy = Application.InputBox("Select range to copy", Type:=8)
If Err.Number <> 0 Then End
On Error GoTo 0
Cpy = Cpy.Address

On Error Resume Next
Set Dest = Application.InputBox("Select range to Paste to:", Type:=8)
If Err.Number <> 0 Then End
On Error GoTo 0

Dest = Dest.Address(external:=True)
On Error Resume Next
ActiveSheet.Range(Cpy).Copy Destination:=Range(Dest) 'Range("B11")
Set Cpy = Nothing
Set Dest = Nothing
If Err.Number <> 0 Then
MsgBox Err.Number & " := " & Err.Description
End If
End Sub

Sorry for the followup confusion! Thanks for the help!! I tacked it to a button, and i makes this easir to keep track of