How to Use VBA Code to Copy Cells by Double Clicking in Excel

In this article, we will learn how to copy a cell to the next empty cell in a different column and sheet by double clicking it, using VBA code in Excel. This can be useful when you have a list of items in one sheet and you want to copy some of them to another sheet in a random order.

Basic Theory

To achieve this task, we need to use the following concepts:

• Events: Events are actions that occur in Excel, such as opening a workbook, changing a cell value, or double clicking a cell. We can write VBA code to run when a specific event occurs, using the Workbook, Worksheet, or Application objects. For example, the Worksheet_BeforeDoubleClick event runs when a cell is double clicked on a worksheet.
• Range: A range is a group of one or more cells in Excel. We can use the Range object to refer to a range and manipulate its properties and methods. For example, the Range.Copy method copies the range to the clipboard or to another location.
• Offset: The Offset property returns a range that is offset from a given range by a certain number of rows and columns. For example, Range("A1").Offset(1, 0) returns the range B1.
• End: The End property returns a range that represents the cell at the end of a region that contains data or formatting. For example, Range("A1").End(xlDown) returns the last cell in column A that has data or formatting.
• Worksheet: A worksheet is a single page in a workbook that contains cells, formulas, charts, and other objects. We can use the Worksheet object to refer to a worksheet and manipulate its properties and methods. For example, the Worksheet.Name property returns or sets the name of the worksheet.

Procedures

To copy a cell to the next empty cell in a different column and sheet by double clicking it, we need to follow these steps:

• Step 1: Insert a new module in the Visual Basic Editor (VBE) and write the following code:

' Declare a public variable to store the destination sheet name
Public DestSheet As String

' Assign the destination sheet name in the Workbook_Open event
Private Sub Workbook_Open()
    DestSheet = "Sheet2" ' Change this to your destination sheet name
End Sub

This code declares a public variable DestSheet that stores the name of the destination sheet where we want to copy the cells. We assign the value of this variable in the Workbook_Open event, which runs when the workbook is opened. You can change the value of DestSheet to your desired sheet name.

• Step 2: In the worksheet where you have the list of items, write the following code in the Worksheet_BeforeDoubleClick event:

' Copy a cell to the next empty cell in a different column and sheet by double clicking
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' Check if the double clicked cell is in column A
    If Target.Column = 1 Then
        ' Copy the cell value
        Target.Copy
        ' Find the next empty cell in column A of the destination sheet
        Dim NextCell As Range
        Set NextCell = Worksheets(DestSheet).Range("A1").End(xlDown).Offset(1, 0)
        ' Paste the cell value
        NextCell.PasteSpecial xlPasteValues
        ' Clear the clipboard
        Application.CutCopyMode = False
        ' Cancel the default action of double clicking
        Cancel = True
    End If
End Sub

This code runs when a cell is double clicked on the worksheet. It checks if the double clicked cell is in column A, which is where we have the list of items. If yes, it copies the cell value and finds the next empty cell in column A of the destination sheet using the End and Offset properties. Then, it pastes the cell value using the PasteSpecial method with the xlPasteValues argument, which only pastes the values and not the formats. Finally, it clears the clipboard and cancels the default action of double clicking, which is editing the cell.

• Step 3: Save the workbook and test the code by double clicking any cell in column A of the source sheet. You should see the cell value copied to the next empty cell in column A of the destination sheet.

Comprehensive Explanation

The following table explains the code line by line:

Scenario and Example

To illustrate how the code works, let’s use the following scenario and example:

• We have a list of fruits in column A of Sheet1, as shown below:

• We want to copy some of the fruits to column A of Sheet2 in a random order by double clicking them, as shown below:

• To do this, we follow these steps:
  • We open the workbook and the VBE assigns the value "Sheet2" to the DestSheet variable in the Workbook_Open event.
  • We double click the cell A4 on Sheet1, which contains the value Cherry.
  • The VBE runs the Worksheet_BeforeDoubleClick event and checks if the Target cell is in column A, which is true.
  • The VBE copies the Target cell value Cherry to the clipboard.
  • The VBE finds the next empty cell in column A of Sheet2, which is A2, and assigns it to the NextCell variable. – The VBE pastes the value Cherry from the clipboard to the cell A2 on Sheet2. – The VBE clears the clipboard and cancels the default action of double clicking. – We see the value Cherry copied to the cell A2 on Sheet2, as shown below:

    Table

    A
    Fruits
    Cherry

    - We repeat the same steps for the cells `A7`, `A2`, and `A5` on `Sheet1`, which contain the values `Fig`, `Apple`, and `Date`, respectively.
    - We see the values `Fig`, `Apple`, and `Date` copied to the cells `A3`, `A4`, and `A5` on `Sheet2`, in that order, as shown below:
    

    Table

    A
    Fruits
    Cherry
    Fig
    Apple
    Date

Other Approaches

There are other possible approaches to copy a cell to the next empty cell in a different column and sheet by double clicking, such as:

• Using formulas instead of VBA code, such as =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) in the destination sheet, which copies the value from the source sheet if it is not blank, and leaves the cell blank otherwise. However, this approach requires the destination sheet to have the same number of rows as the source sheet, and it does not allow the user to choose which cells to copy in a random order.
• Using the built-in features of Excel, such as the Fill command, which can copy a cell to adjacent cells by dragging the fill handle or using the keyboard shortcuts. However, this approach requires the user to select the destination cells manually, and it does not work across different sheets.