Remote Copy and Paste, version 1

Introduction

Recently Allen had a reader how to copy from one area, paste to another, and not leave the cell where he was.  I could see this as useful for presentations and what-ifs where someone wants to see the different results from a variety of data-sets "live" or as calculations progressed.  This is much more dramatic than having the results disappear from the screen while the copy-paste action is going on, then show the screen. 

Divergent Reading of the Problem

When I read the final tip I was surprised that the published suggestions focused on copying and pasting a single cell.  As I read it, I saw the ability to copy/paste a range, as one might do.  As a result, what I saw were things like a macro to copy-paste from a specific cell to another specific cell and a userform like below where one would type in each single cell's column/row address, a form similar to the one below.

My Suggestions

I had several suggestions,   Off the top of my head, here are a couple-few

• Assuming there are only a few results of interest, use Excel's Watch window.
• Assuming all of the data is in a single workbook, one could 
• Open a second Window, 
• Position the open Windows so the results are always visible, 
• Switch focus from the "results" window to the one used for copy-paste
• Copy-Paste
• Although it does not have focus, the results are shown as the workbook re-calculates
• Have a userform with a copy-from range and a copy-to range.  I did not develop this any further but this solution is the focus of this post.

  

  Remote Copy/Paste UI 1st Prototype

Implementing Remote Copy and Paste, version 1

The User Interface

After thinking about the UI, I decided the wording needed to be a little different in order to make it more in common with normal Excel.  Below is the final user form for this version.

Figure 2 - Remote Copy and Paste 

The above form is named "ufCopyPasteRemote".  It has only has two types of active controls: 

• RefEdits to select the ranges for copying and pasting
• reSource is the "Copy from;" field
• reTarget is the "Paste to;" field
• Command Buttons for executing the copy-paste and canceling before execution.
• cbtnCPExecute is the Copy/Paste button
• cbtnCancel is the Cancel button

Userform Code

The basic code is much simpler than I expected and error handling somewhat straightforward, too.  The follow code is in the ufCopyPasteRemote object module.

Private Sub cbtnCancel_Click()

    Application.EnableEvents = False

    With Me 

        'The following two lines can be commented out if  the desire is to retain the last entries for the next usage.

        ' Version 2 will have the ability to keep or clear based on a checkbox on the form.

        .reSource.Value = ""

        .reTarget.Value = ""

        .Hide

    End With

    Application.EnableEvents = True

End Sub

Private Sub cbtnCPExecute_Click()

    On Error GoTo ERR_cbtnCPExecute_Click

    With Me

        Range(.reSource.Value).Copy Range(.reTarget.Value)

        .reSource.Value = ""

        .reTarget.Value = ""

        .Hide

    End With

    GoTo EXIT_cbtnCPExecute_Click

ERR_cbtnCPExecute_Click:

    MsgBox prompt:="Not able to perform Copy & Paste Remote.   Check the entries for 'Copy from:' 'and Paste to:'." _

            & "They must be valid range addresses or Named ranges.", _

           Title:="Remote Copy and Paste", _

           Buttons:=vbOKOnly + vbExclamation

EXIT_cbtnCPExecute_Click:

    On Error GoTo 0

End Sub

Normal Module Code

In order to make this available to the user a Sub must be included to Show the userform.  Once this is included, the ability to perform remote copy and paste is available through the macros dialog (Alt-F8) can be assigned to an icon on the Ribbon or Quick Access Toolbar, and can be included in a customized Context (right-click) Menu.

Sub RemoteCopyPaste()

    ufCopyPasteRemote.Show

End Sub

Lessons Learned

In order to allow the RefEdit controls to find other workbooks the form must not be modal

Shortly after the form was set to non-modal Excel went "out to lunch".  Any click within Excel would just result in a beep.  Could not use Break key or Reset in VBE to release it.

Coming Attractions

Before I published this I started on a series regarding the building of a more robust Copy/Paste Remote tool.  I'm on the second installment, but only about 1/2 through the requirements.