ramlicious Blogs by Tina & Prabhu

October 11, 2014

Comparing Excel Worksheets using VBA

Filed under: Scripting — Prabhuram @ 9:58 am

My wife asked me if I could help her with a macro that will compare two Excel Worksheets to generate a variance or a diff report. Here it is;

Sub CompareSheets(Sheet1 As Worksheet, Sheet2 As Worksheet, RangeToCompare As String, SkipFirstRow As Boolean)
    Dim i As Integer, j As Integer
    Dim val1, val2
    Dim ResultSheet As Worksheet
    Set ResultSheet = Application.Sheets.Add()
    ResultSheet.Name = "ResultSheet" & Format(DateTime.Date, "yyyymmdd") & "-" & Format(DateTime.Time, "hhmmss")
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
            If j = 1 Or (SkipFirstRow = True And i = 1) Then
                ResultSheet.Cells(i, j) = Sheet1.Cells(i, j)
                val1 = Sheet1.Cells(i, j)
                val2 = Application.WorksheetFunction.VLookup(Sheet1.Cells(i, 1), Sheet2.Range(RangeToCompare), j, False)
                ResultSheet.Cells(i, j) = (val1 = val2)
            End If

        Next j
    Next i
End Sub

This Sub-routine can be simply called like this:

Sub Test()
    CompareSheets Application.Sheets(2), Application.Sheets(3), "A:E", True
End Sub

No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URL

Sorry, the comment form is closed at this time.

Powered by WordPress