How to Transfer Specific Data from One Sheet to another through VBA in Microsoft Excel

In this article, we will use change event of worksheet to transfer data one sheet to another, depending upon a certain condition.

Raw data consists of customer details, which includes Name, Street Address, City, Region, Country and Phone number.


In this article, we want to transfer data of those customers who are not eligible for a particular offer. The last column contains “Not” as value, if a customer is not eligible for a particular offer. We want to copy those 'not eligible' customers to “NotEligibleData” sheet.



Code explanation

If Target.Column = 7 Then

The above code is used to restrict change event of worksheet to execute only when value in 7th column is changed.


The above code is used to delete the value from range A2:I600

Sheets("Main").Cells(i, "G").EntireRow.Copy Destination:=Sheets("NotEligibleData").Range("A" & Rows.Count).End(xlUp).Offset(1)

The above code is used to copy non-eligible customers to "NotEligibleData" sheet.


Please follow below for the code

Private Sub Worksheet_Change(ByVal Target As Range)

'Declaring variables
Dim i, Lastrow As Long

'Execute code if value in seventh column is changed
If Target.Column = 7 Then

    'Getting the row number of last cell
    Lastrow = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
    'Deleting any previous data from "NotEligibleData" sheet
    'Looping from tenth row to last row
    For i = 10 To Lastrow
        'If value in G column of the row is "Not" then copy the row to destination sheet
        If Sheets("Main").Cells(i, "G").Value = "Not" Then
            Sheets("Main").Cells(i, "G").EntireRow.Copy Destination:=Sheets("NotEligibleData").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next i
End If


End Sub


If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at


Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.