Select Worksheet Where Data is Pasted
 

Hi All,

I would appreciate help with the following:

I have a user form with 2 regedit boxes on it.

The first regedit box allows the user to select a range to copy while
the second one allows the user to select a range to paste too.

My problem is that if the copy range is on a different worksheet then
the worksheet to which the values are pasted the code always
returns to the worksheet where the data was copied from when
completed. I simply want the code to exit on the worksheet where the
data was
pasted.

For example, if a user selects range A1:A5 on sheet 1 and then decides
to paste the range to C1 :C5 on sheet 3, worksheet 3 should be the
active
worksheet once the code has been executed.

Seems like it should be pretty simple but it is driving me crazy.

Thanks,

Steve

Select Worksheet Where Data is Pasted
 

I recall that RefEdit controls are pretty flakey, unless used in exactly the right way.
I've got stable RefEdit code tucked away somewhere, but this isn't it.
This just shows how to activate the range's worksheet.

Private Sub CommandButton1_Click()
Dim rngCopy As Range, rngPaste As Range

On Error GoTo e

Set rngCopy = Range(RefEdit1.Text)
Set rngPaste = Range(RefEdit2.Text)

rngCopy.Copy rngPaste

rngPaste.Worksheet.Activate
r: Exit Sub
e: MsgBox Err.Description
Resume r
End Sub


Cheers,
Rob



On 14-Dec-2009 18:47, sgltaylor wrote:
Hi All,

I would appreciate help with the following:

I have a user form with 2 regedit boxes on it.

The first regedit box allows the user to select a range to copy while
the second one allows the user to select a range to paste too.

My problem is that if the copy range is on a different worksheet then
the worksheet to which the values are pasted the code always
returns to the worksheet where the data was copied from when
completed. I simply want the code to exit on the worksheet where the
data was
pasted.

For example, if a user selects range A1:A5 on sheet 1 and then decides
to paste the range to C1 :C5 on sheet 3, worksheet 3 should be the
active
worksheet once the code has been executed.

Seems like it should be pretty simple but it is driving me crazy.

Thanks,

Steve