Compare every cell of every worksheet in two workbooks

[jscranton]

I am trying to track down changes between 120 pairs of workbooks (that should be identical but someone changed some data).

I have a list of filePaths for both workbooks. Plan is to open both, loop through each worksheet in both, load the used range of each worksheet into two arrays, compare each, and then flag errors.

Two issues:

(1) I am stuck on the variables for loading the used range into two arrays.

(2) For the second workbook "rWB", what's the easiest way to tell if a worksheet that exist in oWB doesn't exist in the potentially changed worksheet? I suppose I could just create a UDF to test it but wanted to see if there was an easier way using an error handler.

For i = 2 To LR

    oPath = ws.Range("B" & i).Value
    rPath = ws.Range("D" & i).Value
    
    Set oWB = Workbooks.Open(Filename:=oPath)
    Set rWB = Workbooks.Open(Filename:=rPath)
    hasErrors = False
    
   For Each w In oWB.Sheets
   
'Here is where I am stuck


        Set r = w.UsedRange


        varSheetA = ?
        varSheetB = ?


        For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
            For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
                If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
                    ' Cells are identical.
                    ' Do nothing.
                Else
		     hasErrors = True
		End if
            Next iCol
        Next iRow
    Next w

 

[jscranton]

I figured this out. Now to figure out how to tell if a whole worksheet is missing.

Thanks,

J

   For Each w In oWB.Sheets   
        Set r = w.UsedRange
        rAdd = r.Address


        varSheetA = oWB.Sheets(w.Name).Range(rAdd)
        varSheetB = rWB.Sheets(w.Name).Range(rAdd)

 

[Fluff]

You can use a function like

Public Function ShtExists(ShtName As String, Optional wBk As Workbook) As Boolean
    If wBk Is Nothing Then Set wBk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(wBk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

For Each w In oWB.Sheets
      
   If SheetExists(w.Name, rwb) Then
           Set r = w.UsedRange
           '
           '
           '
           '
   End If
Next w