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 WorkbookWorksheet, 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:

Table

Line Explanation
Public DestSheet As String This line declares a public variable DestSheet of type String, which can store a text value. A public variable can be accessed from any module or worksheet in the workbook.
DestSheet = "Sheet2" This line assigns the value "Sheet2" to the variable DestSheet. This means that the destination sheet name is Sheet2. You can change this value to your desired sheet name.
Private Sub Workbook_Open() This line starts a private subroutine named Workbook_Open, which is an event procedure that runs when the workbook is opened. A private subroutine can only be accessed from the module where it is declared.
End Sub This line ends the subroutine.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) This line starts a private subroutine named Worksheet_BeforeDoubleClick, which is an event procedure that runs before a cell is double clicked on the worksheet. It has two parameters: Target, which is a Range object that represents the cell that is double clicked, and Cancel, which is a Boolean variable that determines whether to cancel the default action of double clicking.
If Target.Column = 1 Then This line starts an If statement that checks if the column number of the Target cell is equal to 1, which means that the cell is in column A.
Target.Copy This line copies the Target cell to the clipboard using the Copy method of the Range object.
Dim NextCell As Range This line declares a local variable NextCell of type Range, which can store a reference to a range of cells. A local variable can only be accessed from the subroutine where it is declared.
Set NextCell = Worksheets(DestSheet).Range("A1").End(xlDown).Offset(1, 0) This line assigns a range to the variable NextCell using the Set keyword. The range is obtained by using the Worksheets collection to refer to the worksheet with the name stored in the DestSheet variable, then using the Range property to refer to the cell A1 on that worksheet, then using the End property with the xlDown argument to move to the last cell in column A that has data or formatting, then using the Offset property with the arguments 1 and 0 to move one row down and zero columns to the right, which is the next empty cell in column A.
NextCell.PasteSpecial xlPasteValues This line pastes the value from the clipboard to the NextCell range using the PasteSpecial method of the Range object with the xlPasteValues argument, which only pastes the values and not the formats.
Application.CutCopyMode = False This line clears the clipboard by setting the CutCopyMode property of the Application object to False.
Cancel = True This line sets the value of the Cancel variable to True, which cancels the default action of double clicking, which is editing the cell.
End If This line ends the If statement.

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:

Table

A
Fruits
Apple
Banana
Cherry
Date
Elderberry
Fig
Grape
  • We want to copy some of the fruits to column A of Sheet2 in a random order by double clicking them, as shown below:

Table

A
Fruits
Cherry
Fig
Apple
Date
  • 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.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *