Sync data in two different excel files

[Stefano_1990]

Hi guys

I try to outline it as clearly as I can:
File1 and File2 contain 2 columns, part number and description.
File1 has one worksheet while File2 has three.

In File1 there are 2000 components and in File2 there are only about 300.

All I need to do is sync all three worksheets in File2 based on the data in File1 but without adding additional columns. So only update the 300 rows that are in File2. I was trying to figure out how to do that half the day. I have never done any visual basic programming in my life..

I came to you guys because I have the feeling that you guys would solve this in a matter of minutes what takes me hours...

I really appreciate your help.

Kind regards
Stefano

 

[Saagar]

Stefano - You can put code in File2 to update File1. The reason I suggested putting code in File1 is to minimize confusion so you can click the button in File1 to update File1 and button in File2 to sync File2.

I will provide you code to update File1 based on values in File2 when I get some time later today.

 

[Saagar]

Here is the code to update File1 based on changes in File2. It will ignore those parts that exist in File2 but not in File1.

In File2, on worksheet1, add another button from the "control toolbox". Double click the button. A VB window will appear as before and you will see something like this:

Private Sub CommandButton2_Click()

End Sub

Copy paste this code between the above lines and update the values in Red to suit your file names and sheet names.

    thisfile = ThisWorkbook.Name
    shts = Array("Sht1", "Sht2", "Sht3")

    sourcefile = "Excel Macros2.xls"
    updatesht = "sht123"
    
    j = 0
    startrow = 2
    
    Windows(sourcefile).Activate
    Sheets(updatesht).Range("A:A").Select
    Selection.AutoFilter
    
    For j = 0 To UBound(shts)
        Windows(thisfile).Activate
        lastrow = Sheets(shts(j)).Range("A63756").End(xlUp).Row
        For i = startrow To lastrow
            Windows(thisfile).Activate
            Sheets(shts(j)).Select
            
            filtercriteria = "=" & Sheets(shts(j)).Cells(i, 1)
            
            Windows(sourcefile).Activate
            Sheets(updatesht).Select
           
            Set searchrange = Sheets(updatesht).Range("A:A")
            entrycount = WorksheetFunction.CountIf(searchrange, filtercriteria)
            
            If entrycount > 0 Then
            
                Selection.AutoFilter Field:=1, Criteria1:=filtercriteria, Operator:=xlAnd
                Sheets(updatesht).Range("A1").Select
                getrow = Sheets(updatesht).Range("A1").End(xlDown).Row
                
                
                Windows(thisfile).Activate
                Sheets(shts(j)).Range("C" & i, "G" & i).Select
                Application.CutCopyMode = False
                Selection.Copy
        
                Windows(sourcefile).Activate
                Sheets(updatesht).Cells(getrow, "C").Select
                ActiveSheet.Paste
            End If
            
        Next
    Next
    
    Windows(sourcefile).Activate
    Sheets(updatesht).Select
    Selection.AutoFilter

Let me know if this works the way you want.