Hello everyone, I need help to create a macro code that compares two excel files automatically.
I have a price list that I need to update every month and when the new file is uploaded, we put the old file in one with the old ones and only use the new one. So I have an old file and a new file with the same information, the only thing that can change is the purchase price or the quantity. So I only have two columns to compare.
So I'd like to find a macro code that helps me compare these two files automatically, and that allows me to put the references that actually have a change in a new folder that will be the archive of the changes. In this third sheet, when there's a change in the price list, I want the line for that reference to be copied into this file.
I've done something similar in the past but haven't found a solution for this one.
Here's the code I have at the moment, but it doesn't work :
File1 = is the old file.
File2 = the new file.
Sheet1 = the sheet that have to be compare from the old file.
Sheet2 = the sheet that have to be compare from the new file.
SummaryResults = the archive file.
I have a price list that I need to update every month and when the new file is uploaded, we put the old file in one with the old ones and only use the new one. So I have an old file and a new file with the same information, the only thing that can change is the purchase price or the quantity. So I only have two columns to compare.
So I'd like to find a macro code that helps me compare these two files automatically, and that allows me to put the references that actually have a change in a new folder that will be the archive of the changes. In this third sheet, when there's a change in the price list, I want the line for that reference to be copied into this file.
I've done something similar in the past but haven't found a solution for this one.
Here's the code I have at the moment, but it doesn't work :
File1 = is the old file.
File2 = the new file.
Sheet1 = the sheet that have to be compare from the old file.
Sheet2 = the sheet that have to be compare from the new file.
SummaryResults = the archive file.
VBA Code:
Sub CompareExcelversionFiles()
Dim File1 As Workbook
Dim File2 As Workbook
Dim SummaryResults As Workbook
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim SheetHist As Worksheet
Dim line As Long
Dim lastLineHist As Long
' Specify path and file names
Set File1 = Workbooks.Open("/Users//Downloads/Test/File1.xlsx")
Set File2 = Workbooks.Open("/Users//Downloads/Test/File2.xlsx")
Set history = Workbooks.Open("path_to_history.xlsx")
' Specify the name of the sheets to be compared
Set Sheet1 = Sheet1.Sheets("Sheet1")
Set Sheet2 = Sheet2.Sheets("Sheet2")
Set SheetHist = History.Sheets("SummaryResults")
' Set the last line of archive
derniereLigneHist = feuilleHist.Cells(feuilleHist.Rows.Count, 1).End(xlUp).Row
' Loop through lines in both files
For line = 1 To Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
' Checks if the line in version 2 is different from version 1
If Not Sheet1.Cells(row, 1).Value = Sheet2.Cells(row, 1).Value Then
' Copy line from version 1 to history
Sheet1.Rows(line).Copy sheetHist.Rows(lastLineHist + 1)
lastLineHist = lastLineHist + 1
End If
Next line
' Close files without saving them
File1.Close SaveChanges:=False
File2.Close SaveChanges:=False
archvie.Close SaveChanges:=True
' Free memory
Set File1= Nothing
Set File2 = Nothing
Set archive= Nothing
Set File1= Nothing
Set File2 = Nothing
Set feuilleHist = Nothing
MsgBox "Comparison complete. Modified lines have been copied to history."
End Sub