Automatically Extract Entire Row Data to New Sheet When Clicked in Excel

In this tutorial, you will learn how to automatically extract entire row the data to new sheet when clicked in Microsoft Excel.

Look at below example. There are two sheets in a workbook. First sheet (data) has the information of customers row wise. Our requirement here is to automatically extract the entire row data to another sheet (new_data) based on cell click event.

In the image above, when clicked on different customer names in “data” sheet, entire row information is automatically extracting to the “new_data” sheet.

Steps of Doing This in Excel

Follow these steps to achieve this in Excel.

Step 1: Get the VBA Code

Since, Excel does not have any default function or tool to do this, we need to use a VBA code. Get the VBA code from below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim clickedRow As Long
    Dim lastColumn As Long
    Dim destinationRange As Range
    Dim copyRange As Range
    
    Set wsSource = ThisWorkbook.ActiveSheet
    Set wsDestination = ThisWorkbook.Sheets("new_data") ' Change to your destination sheet name
    Set destinationRange = wsDestination.Range("A" & wsDestination.Rows.Count).End(xlUp).Offset(1, 0)
    
    ' Checking if the clicked cell is in the first column
    If Target.Column = 1 Then
        clickedRow = Target.Row
        lastColumn = wsSource.Cells(clickedRow, wsSource.Columns.Count).End(xlToLeft).Column
        Set copyRange = wsSource.Range(wsSource.Cells(clickedRow, 1), wsSource.Cells(clickedRow, lastColumn))
        
        ' Copying and pasting the row to the destination sheet
        copyRange.Copy destinationRange
    End If
End Sub

Step 2: Copy Above Code and Add it to Your Excel Workbook

Now copy the code given above and add the code to your excel workbook. For this, follow these steps.

1. Click on “Developer” tab.
2. Click on “Visual Basics”
3. Double Click on the sheet name, where you have the source data. “data” sheet in this example.
4. Paste the code.

Step 3: Edit the Sheet Name

Now you need to edit the sheet name where you want the keep the extracted row data. Replace the sheet name with the name that is in your workbook. Then close the VBA editor window.

After completing all these steps, when you click on the customer names, the entire row data will automatically extract to the new sheet.