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 7^th column is changed.

Sheets("NotEligibleData").Range("A2:I600").ClearContents

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
    Sheets("NotEligibleData").Range("A2:I600").ClearContents
    
    '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

Range("A1").Select

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 info@exceltip.com