Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
May 18, 2018

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 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

 

excel
  • Matt Mickle's avatar
    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's avatar
    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

     

     

     

    • Lorenzo Kim's avatar
      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's avatar
        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

Resources