I need to automate comparison between two sheets and then email if mismatch during a batch process. My sheet looks like this:
Head1 Head1 Head1
Column1 Column2 Column3
Head2 Head2 Head2
Head3 Head3 Head3
Head4 Head4 Head4
Info1 Info2 Info3 Info4 Row1 100 1,001 2,001
Info1 Info2 Info3 Info4 Row2 101 1,002 2,002
Info1 Info2 Info3 Info4 Row3 102 1,003 2,003
Info1 Info2 Info3 Info4 Row3 103 1,004 2,004
Info1 Info2 Info3 Info4 Row4 104 1,005 2,005
So while I have the vba code (which currently uses msgbox but will be amended to email recipients):
Option Explicit
Dim ws As Worksheet
Dim rw As Long, col As Long, lastrw As Long, lastcol As Long
Sub CompareData()
For Each ws In Workbooks("Book1.xlsm").Worksheets
lastrw = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
lastcol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
rw = 6
With Workbooks("Book2.xlsm").Sheets(ws.Name)
Do Until rw > lastrw
col = 6
Do Until col > lastcol
If ws.Cells(rw, col).Value <> .Cells(rw, col).Value Then
MsgBox "Values don't match!"
Exit Sub
End If
col = col + 1
Loop
rw = rw + 1
Loop
End With
Next
MsgBox "All sheets match"
End Sub
Now I am not sure how to achieve this via vbscipt. Then I can invoke the script using "cscript myvbscript.vbs" in batch to do this automatically.