Compare two excel files and bring the non existing entries from 1st excel file to 2nd excel file

deba2020

New Member
Joined
Jan 8, 2020
Messages
26
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
We have 2 excel files, we want to bring all the data from 1st excel file to 2nd excel file which are not there in 2nd excel file.
1st excel file name : Store Linking File.xlsm
2nd excel file name : Inward.xlsm
The existing code which we have written is taking much time because of the loops.
Please help with a code which will be more efficient than the existing one and takes less time to bring data from 1st file.

Stores Linking File.xlsm
B
2GRN
Sheet1


Inward.xlsm
A
310:26:00
Sheet1



Existing code is as follows:

VBA Code:
'module for updating GR details from Store linking file
Sub update_GRDetails1()
ActiveSheet.Unprotect Password:="123456"
Dim i, w1, w2 As Worksheet
Dim FirstR, LastR, n, founR, x, x2 As Long
Dim GRN1 As String
Dim FounD As Range
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    Workbooks.Open "\\bpild6987-01\Linking File\Stores Linking File.xlsm", ReadOnly:=True, UpdateLinks:=True
    ActiveWindow.Visible = True
    Set w1 = Workbooks("Stores Linking File.xlsm").Sheets("Sheet1")
    Set w2 = Workbooks("Inward.xlsm").Sheets("Sheet1")
    'Find the last Non Zero Cell in Date Column
        x2 = w1.Range("K" & Rows.Count).End(xlUp).Row
        Do Until w1.Range("k" & x2) <> 0
        x2 = x2 - 1
        Loop

    FirstR = w2.Columns("B:B").Find(What:="GRN").Row

   For x = FirstR + 2 To x2
  
   w1.Activate
       
     GRN1 = w1.Cells(x, 2) & w1.Cells(x, 4) & w1.Range("R" & x).Value
     w2.Activate
     Set FounD = w2.Columns("AZ:AZ").Find(What:=GRN1)
     If FounD Is Nothing Then
    
     w2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = w1.Range("B" & x).Value
     w2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = w1.Range("C" & x).Value
     w2.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = w1.Range("D" & x).Value
     w2.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = Mid(w1.Range("D" & x).Value, 3, 4)
     w2.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Value = w1.Range("E" & x).Value
     w2.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Value = w1.Range("F" & x).Value
     w2.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Value = w1.Range("K" & x).Value
     w2.Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Value = w1.Range("J" & x).Value
     w2.Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-2]* RC[-1]"
     w2.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Value = w1.Range("N" & x).Value
     w2.Cells(Rows.Count, 16).End(xlUp).Offset(1, 0).Value = w1.Range("O" & x).Value
     w2.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).Value = w1.Range("S" & x).Value
     w2.Cells(Rows.Count, 2).End(xlUp).Offset(0, 50).Value = w1.Range("b" & x).Value & w1.Range("d" & x).Value & w1.Range("R" & x).Value
         
     End If
    
   Next x
  
   w2.Activate
  
   Workbooks("Stores Linking File.xlsm").Close savechanges:=False
  
   'Delete the rows in which GRN or Quantity is Zero
   LastR = w2.Cells(Rows.Count, 7).End(xlUp).Row
   For i = LastR To FirstR + 1992 Step -1
   If Cells(i, 2).Value = 0 Or Cells(i, 7).Value = 0 Then
   Cells(i, 6).EntireRow.Delete
   End If
   Next i

   Range(Range("A1993").End(xlDown).Offset(1, 0), Cells(Cells(Rows.Count, 7).End(xlUp).Row, 52)).Select
   Range("A1993:AZ1993").Copy
   Selection.PasteSpecial Paste:=xlPasteFormats
  
   ActiveSheet.Protect Password:="123456", AllowFiltering:=True
   ActiveWorkbook.Save
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True

End Sub
 
Last edited by a moderator:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Not sure if this helps...
The code at the link compares 2 workbooks and colours the cells that are different. U can have data in workbook#1 that is not in workbook#2 AND U can have data that's in workbook#2 that's not in workbook#1. I'm not sure that applies here for U? Do U want to only check workbook#1 for data that's not in workbook#2? What if data exists but is not the same? The code can be adjusted to update workbook#2 and/or workbook#1 rather than changing the cell colour fairly easily. As I recall, the code is fairly quick but will be dependent upon how much data is in the 2 workbooks. HTH. Dave
 
Upvote 0
Yes, we are only comparing data from workbook1 with data from workbook2, and if the data from workbook1 is missing from workbook2, we will add it to workbook2.
And in workbook2 we are trying to copy the format of above cells and apply it to the newly added data.
 
Upvote 0
After reviewing your code more carefully, your thread title is somewhat misleading. You are only comparing 1 sheet and only certain rows and certain columns of the 2 workbooks. I don't think that the linked code will help with this. I do have some suggestions..
1)When declaring variables...
Code:
Dim FirstR, LastR, n, founR, x, x2 As Long
The statement above only declares the "x2" as Long, the rest remain Variant ie. U need to declare each variable.
2)U start your code with...
Code:
On Error Resume Next
which does not address any errors, may produce erroneous results and who knows, maybe extends the time of code execution. I would guess U have included this in case some comparison cells are on error? It would be better to code to avoid errors. However, if U remove this code and U are going to mess with the screen updating, alerts, events and calculations, then U will need to code to ensure that if an error does occur and the code is not fully executed, then these are returned to normal.
3)U may want to trial controlling the events and calculations which will improve speed...
Code:
'at the start
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'at the end
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Hopefully, some of this is helpful. Perhaps others have alternate suggestions. Dave
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top