How to compare data between two sheets, extract the changed lines and copy into another sheet

[frostworks]

I have the following sheet below called Sheet 1.
There is another copy of Sheet 1 called Sheet 2.
Column D (Car) in Sheet 1 is editable.
Suppose someone using the sheet make changes to Column D in line Request ID 1 and Request ID 4,
how do i code a macro to compare and identify with Sheet 2 that D5 and D8 has been changed, and thus Row 5 and Row 8 will be copied into Sheet 3?

 

[SanjayGMusafir]

I knew Excel has a feature to highlight changes...

I don't know it shall work for you or not...

How to track changes in Excel

The tutorial explains the basics of Excel Track Changes and shows how to highlight changes on screen, list changes in a separate sheet, accept and reject changes, as well as monitor the last changed cell.

www.ablebits.com

or

Track changes in a Shared Workbook - Microsoft Support

Use Track Changes to track, maintain, and display information about the changes that are made to a shared workbook.

support.microsoft.com

 

[frostworks]

thanks for the tip! however, this will be part of a longer macro i'm coding, so i would still require the vba code to do it...

 

[SanjayGMusafir]

so i would still require the vba code to do it...

I'm no VBA expert. Hope some VBA expert shall revert you.

 

[PeteWright]

Hi @frostworks

Here's some code to test:

Option Explicit
Private Sub CompareSheets()
   Dim Sheet1 As Worksheet: Set Sheet1 = Worksheets("Sheet 1")
   Dim Sheet2 As Worksheet: Set Sheet2 = Worksheets("Sheet 2")
   Dim Sheet3 As Worksheet: Set Sheet3 = Worksheets("Sheet 3")
   Dim FirstRow As Long
   Dim LastRow As Long
   Dim CurrentRow As Long

   FirstRow = 5
   LastRow = 8

   For CurrentRow = FirstRow To LastRow
      If Sheet1.Range("D" & CurrentRow).Value <> Sheet2.Range("D" & CurrentRow).Value Then
         Sheet1.Rows(CurrentRow).Copy Sheet3.Rows(CurrentRow)
      End If
   Next CurrentRow
End Sub

Please note: This code will only work on the data range C4:F8
So if any changes are made on Sheet 1 column D5 to D8, the macro will find the changes and paste them on the exact same location in Sheet 3.

Here is the result:

Sheet 1

Sheet 2

Sheet 3 after the macro ran

If you want the VBA code to work with your data, you'll need to provide a little more information.

 

[frostworks]

Hi Pete!

Apologies for the late revert. that looks really useful! How do i go one step further and have the results in sheet 3 tucked nicely into the corner? intention is to have sheet 3 as a database of changed data.

THanks!

 

[Tupe77]

This modification to @PeteWright's code writes the data to the left side of Sheet 3 starting from row 2.

I hope this is suitable for your use, but if you plan to use this in a practical application, a few notes about the operation.

1. Ranges are hardcoded
2. Sheet 1 and Sheet 3 areas must be the same size.
3. The row number of the rows to be compared must be the same on Sheet 1 and Sheet 2.
4. Rows to be added to Sheet 3 are not checked for duplicates. So the same change can be written on Sheet 3 every time it is executed.

Private Sub CompareSheets()
Dim Sheet1 As Worksheet: Set Sheet1 = Worksheets("Sheet 1")
Dim Sheet2 As Worksheet: Set Sheet2 = Worksheets("Sheet 2")
Dim Sheet3 As Worksheet: Set Sheet3 = Worksheets("Sheet 3")
Dim FirstRow As Long
Dim LastRow As Long
Dim CurrentRow As Long

    FirstRow = 5
    LastRow = 8
        For CurrentRow = FirstRow To LastRow
           If Sheet1.Range("D" & CurrentRow).Value <> Sheet2.Range("D" & CurrentRow).Value Then
              Sheet1.Range("C" & CurrentRow & ":" & "F" & CurrentRow).Copy Sheet3.Range("A" & Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1) ' This row is modified
           End If
        Next CurrentRow
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[frostworks]

This modification to @PeteWright's code writes the data to the left side of Sheet 3 starting from row 2.

I hope this is suitable for your use, but if you plan to use this in a practical application, a few notes about the operation.

1. Ranges are hardcoded
2. Sheet 1 and Sheet 3 areas must be the same size.
3. The row number of the rows to be compared must be the same on Sheet 1 and Sheet 2.
4. Rows to be added to Sheet 3 are not checked for duplicates. So the same change can be written on Sheet 3 every time it is executed.

Private Sub CompareSheets()
Dim Sheet1 As Worksheet: Set Sheet1 = Worksheets("Sheet 1")
Dim Sheet2 As Worksheet: Set Sheet2 = Worksheets("Sheet 2")
Dim Sheet3 As Worksheet: Set Sheet3 = Worksheets("Sheet 3")
Dim FirstRow As Long
Dim LastRow As Long
Dim CurrentRow As Long

    FirstRow = 5
    LastRow = 8
        For CurrentRow = FirstRow To LastRow
           If Sheet1.Range("D" & CurrentRow).Value <> Sheet2.Range("D" & CurrentRow).Value Then
              Sheet1.Range("C" & CurrentRow & ":" & "F" & CurrentRow).Copy Sheet3.Range("A" & Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1) ' This row is modified
           End If
        Next CurrentRow
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

Thank you for sharing! unfortunately the use i have for it is unable to comply with conditions 1, 3 and 4.