Selected Answer
Allison
A quick search showed that you used the (copyright?) CompareWorkbooks macro from another site (bettersolutions.com) in your file but inadvertently messed it up- your re-titled sub has now undeclared variables (WS1 and Ws2) and refers to variables from your DoCompare sub. Furthermore,since your macro sheets and TestData 2.xlsx* files both have sheets called Data, CompareWorkbooks tries to add a sheet with the same name as an existing sheet (and so VBA fails).
(* be careful of the space in that name- you got it wrong in DoCompare macro in your file)
You don't need to activate a worksheet for VBA to use it (and it can slow things down) but you do need to set the variables correctly. In the revised file attached I've replaced your macro with this:
Sub DoCompare()
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = ThisWorkbook.Worksheets("Data")
Set WS2 = Workbooks("TruckData 2.xlsx").Worksheets("Data")
Application.ScreenUpdating = False
Call CompareWorkbooks(WS1, WS2)
Application.ScreenUpdating = True
End Sub
which just compares the Data sheets you want to compare (there's little point trying to compare the Glossary sheets IMHO!). It disables screen updating (so Excel works faster), sends WS1 and WS2 to the
CompareWorkbooks macro and once control returns, restores screen updating so you see the new sheet "
Data Comparison".
I've also I've restored the original macro as CompareWorkbooks(ByVal WS1 As Worksheet, ByVal WS2 As Worksheet) but changed the line which creates the new sheet and put it to the end, see bits in bold in the extract below):
'Worksheets.Add.Name = "Comparison " & Worksheets.Count ' new book for the results
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = WS1.Name & " Comparison" ' new SHEET for the results
(original comment changed since it adds a sheet not a workbook!)
That said, the comparison is cell-by-cell (good to see changes between file version if that's what you want) but it doesn't work to compare just Odometer values between vehicles (which you say you want) unless the vehicles appear on the same rows in eack workbook/sheet.
Please note that since you have Excel 365 you "compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets" using the Spreadsheet Inquire add-in. That gives a more intuitive result too. The Microsoft guidance on that is here: Compare workbooks using Spreadsheet Inquire
Hope this helps.
