SOLVED

compare two worksheets if identical

Bronze Contributor

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 help in the correction (I have search the net but couldn't find the right one)

 

inside a sub routine

    If Worksheets("pc1") = Worksheets("pc") Then

        MsgBox "No changes found..the 2 worksheets are identical"

        exit sub

    End If

End Sub

 

the [Worksheets("pc1") = Worksheets("pc")] is hi-lighted to be incorrect - how should this be written?

 

many thanks

 

4 Replies

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.  

 

Spreadsheet Compare.png

 

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:

 

Spreadsheet Compare Formula.png

 

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

 

 

 

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

 

 

best response confirmed by Lorenzo Kim (Bronze Contributor)
Solution

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

Mr Mickle

 

The VBA code worked perfectly!!

Thank you very very much.

1 best response

Accepted Solutions
best response confirmed by Lorenzo Kim (Bronze Contributor)
Solution

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

View solution in original post