VBA to compare workbooks

[SteveDB1]

morning all.
On a fairly regular basis, we need to update our existing workbooks.
Sometimes we stumble across duplicates, and need to make sure that the data
in both books match, so that we don't inadvertantly miss something of
importance.
Aside from going worksheet to worksheet, using IF equations, is where some
means- VBA, that I can use to check both the names of the worksheets, and
their contents with multiple workbooks?

Thank you.
Best.
SteveB.

 

[Office_Novice]

DownLoad Chip Pearsons Compare Add - in

http://www.cpearson.com/excel/Download.htm

 

[OssieMac]

Hi Steve,

The following should do what you want. However, you need to understand that
if the used range in the first workbook is smaller that the used range on a
worksheet in the second workbook then it would omit comparing the additional
range. You can workaround this by running it twice and swapping the workbook
names in the following two lines.

Set wb1 = Workbooks("Test Workbook 1.xls")
Set wb2 = Workbooks("Test Workbook 2.xls")

After first run of program, Change above to

Set wb1 = Workbooks("Test Workbook 2.xls")
Set wb2 = Workbooks("Test Workbook 1.xls")

Also, it stops if the worksheets do not compare and you need to fix them up
first and then re-run the propgram to compare cells.

Run the program from Sub CompareWorkbooks() and it calls the other two subs.

Option Explicit
Dim wbThis As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wsWb1 As Worksheet
Dim wsWb2 As Worksheet
Dim bolFound As Boolean
Dim cel As Range

Sub CompareWorkbooks()

Set wbThis = ThisWorkbook

'Edit to insert your workbook names
Set wb1 = Workbooks("Test Workbook 1.xls")
Set wb2 = Workbooks("Test Workbook 2.xls")

Call WorksheetLoop

If bolFound = False Then 'matching sheet not found
MsgBox "Worksheet " & wsWb1.Name & " in " & wb1.Name & _
Chr(13) & "not found in " & wb2.Name
Exit Sub 'Exit and fix non matching sheet error
Else
Call CompareSheets
End If

End Sub

Sub WorksheetLoop()

'Loop through worksheets in workbook 1
For Each wsWb1 In wb1.Sheets
'Loop through worksheets in workbook 2
'and look for matching name as in workbook 1
bolFound = False
For Each wsWb2 In wb2.Sheets
If wsWb1.Name = wsWb2.Name Then
bolFound = True 'Worksheet matched
Exit For 'Return to calling sub
End If
Next wsWb2
If bolFound = False Then
Exit For
End If
Next wsWb1

End Sub

Sub CompareSheets()

'Loop through the worksheets in first workbook
For Each wsWb1 In wb1.Sheets
'Loop through the cells in the used range
'and compare to cells in the second workbook
For Each cel In wsWb1.UsedRange
If cel <> wb2.Sheets(wsWb1.Name).Cells(cel.Row, cel.Column) Then
MsgBox "Sheet " & wsWb1.Name & " " & cel.Address(0, 0) _
& " Not matched" & Chr(13) & "Make a note of the address"
End If
Next cel
Next wsWb1

End Sub