Tracking changed cells in two identical worksheets

Hi,

I have two worksheets with the same columns. File 1 is my master sheet with
all my rows of data, while File 2 contains only specific rows in File 1. For
example,

File 1 (1000 rows)
Name... City... Age... Group

File 2 (20 of the 1000 rows)
Name... City... Age... Group

File 2 has the most up-to-date data for the 20 of the 1000 rows. Can I
update the data from File 2 into File 1 (for changed cells only) by using a
macro?

I'm not too familiar with VBA code.

Thanks in advance for your help!

Hi,
Try this (on test files!):

Sub FundandCopy()
'
' Replace rows in Sheet1 with corresponding rows in Sheet2
' Assumes unique values e.g names, in each sheet
'
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long, ilastrow1 As Long, ilastrow2 As Long
Dim FindValue As String

Set ws1 = Worksheets("sheet1") ' Master sheet
Set ws2 = Worksheets("sheet2") ' Updates sheet

ilastrow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row ' Assumes name in
Column A
ilastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To ilastrow2 ' Loop through names in sheet2 (row 1 = header)

FindValue = ws2.Cells(i, 1) ' e.g Name

With ws1.Range("a2:a" & ilastrow1) ' find match on name in Sheet1
Set c = .Find(FindValue, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then ' Copy from sheet 2 to sheet 1
ws2.Rows(i).EntireRow.Copy Destination:=ws1.Cells(c.Row, 1)
Else
MsgBox "Name " & FindValue & " not found"
End If
End With

Next i

Set ws1 = Nothing
Set ws2 = Nothing

End Sub


"THT" wrote:

> Hi,
>
> I have two worksheets with the same columns. File 1 is my master sheet with
> all my rows of data, while File 2 contains only specific rows in File 1. For
> example,
>
> File 1 (1000 rows)
> Name... City... Age... Group
>
> File 2 (20 of the 1000 rows)
> Name... City... Age... Group
>
> File 2 has the most up-to-date data for the 20 of the 1000 rows. Can I
> update the data from File 2 into File 1 (for changed cells only) by using a
> macro?
>
> I'm not too familiar with VBA code.
>
> Thanks in advance for your help!
>

Thank you so much, it works perfectly!

"Toppers" wrote:

> Hi,
> Try this (on test files!):
>
> Sub FundandCopy()
> '
> ' Replace rows in Sheet1 with corresponding rows in Sheet2
> ' Assumes unique values e.g names, in each sheet
> '
> Dim ws1 As Worksheet, ws2 As Worksheet
> Dim i As Long, ilastrow1 As Long, ilastrow2 As Long
> Dim FindValue As String
>
> Set ws1 = Worksheets("sheet1") ' Master sheet
> Set ws2 = Worksheets("sheet2") ' Updates sheet
>
> ilastrow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row ' Assumes name in
> Column A
> ilastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
>
> For i = 2 To ilastrow2 ' Loop through names in sheet2 (row 1 = header)
>
> FindValue = ws2.Cells(i, 1) ' e.g Name
>
> With ws1.Range("a2:a" & ilastrow1) ' find match on name in Sheet1
> Set c = .Find(FindValue, LookIn:=xlValues, lookat:=xlWhole)
> If Not c Is Nothing Then ' Copy from sheet 2 to sheet 1
> ws2.Rows(i).EntireRow.Copy Destination:=ws1.Cells(c.Row, 1)
> Else
> MsgBox "Name " & FindValue & " not found"
> End If
> End With
>
> Next i
>
> Set ws1 = Nothing
> Set ws2 = Nothing
>
> End Sub
>
>
> "THT" wrote:
>
> > Hi,
> >
> > I have two worksheets with the same columns. File 1 is my master sheet with
> > all my rows of data, while File 2 contains only specific rows in File 1. For
> > example,
> >
> > File 1 (1000 rows)
> > Name... City... Age... Group
> >
> > File 2 (20 of the 1000 rows)
> > Name... City... Age... Group
> >
> > File 2 has the most up-to-date data for the 20 of the 1000 rows. Can I
> > update the data from File 2 into File 1 (for changed cells only) by using a
> > macro?
> >
> > I'm not too familiar with VBA code.
> >
> > Thanks in advance for your help!
> >