Forum Discussion

Bronze Contributor

May 18, 2018

Solved

compare two worksheets if identical

In the same workbook: I have one worksheet named pc1 , another named pc I want to compare both - if identical (exactly the same) - then exit the sub routine the syntax below is not working - pls ...

excel

Matt Mickle

May 22, 2018

Lorenzo-

Try this sub procedure. I have commented it for better understanding.

Sub TestFormulasAndValues()

    Dim pc As Worksheet
    Dim pc1 As Worksheet
    Dim ColLp As Integer
    Dim RowLp As Integer
    
    'Set worksheets
    Set pc = Worksheets("pc")
    Set pc1 = Worksheets("pc1")
    
    'Cycle through Rows 1 to 11
    For RowLp = 1 To 11
        'Cycle through columns 1 to 5 (A-E)
        For ColLp = 1 To 5
            'Compare Values
            If pc.Cells(RowLp, ColLp).Value = pc1.Cells(RowLp, ColLp).Value Then
                If pc.Cells(RowLp, ColLp).Formula = pc1.Cells(RowLp, ColLp).Formula Then
                    'Do Nothing.... value and formula match...
                Else
                    'Formulas don't match Exit the sub procedure
                    MsgBox "Formulas do not match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
                End If
            Else
                'Values don't match exit the sub procedure
                MsgBox "Values don't match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
            End If
        Next ColLp
    Next RowLp

End Sub

Matt Mickle

Bronze Contributor

May 21, 2018

Lorenzo-

You can't compare an entire spreadsheet and it's associated values / formulas using an expression like the one you have listed. You will need to compare the values in the cells individually. There is a free tool that you can use to do this in a user friendly manner. The tool is called Spreadsheet Compare. I think it comes with the Professional Plus version of office.

You can find the tool here if you have it installed.

If you do not have this software I would recommend creating an additional sheet and then using formulas like this to identify issues:

See attached file for reference If you can assess a sheet using the above then you could just put that formula in VBA to identify if the values are the same....

Sub Test()

    Same = Application.CountIf(Sheets("Comparison").Range("A1:E11"), "FALSE")

    If Same > 0 Then
        MsgBox "Sheets are NOT the same"
    Else
        MsgBox "Sheets are the same"
    End If

End Sub

SheetComparison.xlsx11 KB

Lorenzo Kim

Bronze Contributor

May 21, 2018

Mr Mickle

Thank you for your reply.

I tried the attached file - it worked.

will it work if there are more than 2 worksheets?

how would sub test() be written to compare just the worksheets "pc1" and "pc" out of many worksheets in the workbook?

many thanks

Matt Mickle

Bronze Contributor

May 22, 2018

Lorenzo-

Try this sub procedure. I have commented it for better understanding.

Sub TestFormulasAndValues()

    Dim pc As Worksheet
    Dim pc1 As Worksheet
    Dim ColLp As Integer
    Dim RowLp As Integer
    
    'Set worksheets
    Set pc = Worksheets("pc")
    Set pc1 = Worksheets("pc1")
    
    'Cycle through Rows 1 to 11
    For RowLp = 1 To 11
        'Cycle through columns 1 to 5 (A-E)
        For ColLp = 1 To 5
            'Compare Values
            If pc.Cells(RowLp, ColLp).Value = pc1.Cells(RowLp, ColLp).Value Then
                If pc.Cells(RowLp, ColLp).Formula = pc1.Cells(RowLp, ColLp).Formula Then
                    'Do Nothing.... value and formula match...
                Else
                    'Formulas don't match Exit the sub procedure
                    MsgBox "Formulas do not match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
                End If
            Else
                'Values don't match exit the sub procedure
                MsgBox "Values don't match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
            End If
        Next ColLp
    Next RowLp

End Sub

Lorenzo Kim
Bronze Contributor
May 22, 2018
Mr Mickle

The VBA code worked perfectly!!
Thank you very very much.