Ask a question Report

Macro to compare two excel sheets [Solved/Closed]

annajins - Latest answer on Aug 2, 2012 04:58AM
Hello all,
I need a help...!! I need help to write macro to compare two sheets, sheet1 and sheet2 on same excel book and display the result on the third sheet. The number of rows and columns may not be equal and it can vary each time.
So we need to find the range of columns and rows first..! I am struggling with this. So please extend your helping hands........
I tried the following code to compare the values,
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 12/11/2008 by UBOC User
'
'
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(SIT!RC=PPE!RC,"" "",CONCATENATE(SIT!RC,"" - "",PPE!RC))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:EG2"), Type:=xlFillDefault
Range("A2:EG2").Select
Selection.AutoFill Destination:=Range("A2:EG1614"), Type:=xlFillDefault
Range("A2:EG1614").Select
End Sub
But here I don't know how to get the Range dynamically and implement it in this macro....
Some one Please help......
Read more 
Answer
+26
moins plus
Check this macro

Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
(what:=c.Value, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
'c.EntireRow.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
c.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
c.Offset(0, 2).Copy Worksheets("sheet3").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)


End With 'sheet 2
line1:
Next c
Application.CutCopyMode = False
End With 'sheet 1


End Sub

Roxy- Dec 7, 2011 09:42PM
hey hi there can any one tell me where to add this macro in VBA
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Dec 8, 2011 12:02PM
@Roxy "hey hi there can any one tell me where to add this macro in VBA"

Open workbook
press alt + f11 at same time
click on insert and add a new module
paste the code


press f5 to run the macro
Aks- Aug 2, 2012 04:58AM
hi
i am using the following code to compare two worksheets. The code is working fine. the differences are highlighted. I need a code that if i rectify the diferences in one of the sheets, the previously highlighted cells should return to normal. can somebody help me on this please?

the code is:


Sub All_Diffs_Highlighted()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Cell As Range
Dim sBook As String
'
If Workbooks.Count < 2 Then
MsgBox "Error: Only one Workbook is open" & vbCr & _
"Open a 2nd Workbook and run this macro again."
Exit Sub
End If
'
Set wb1 = ThisWorkbook
For Each wb2 In Workbooks
If wb2.Name <> wb1.Name Then Exit For
Next
'
On Error Resume Next
ReDo1:
Application.DisplayAlerts = False
sBook = Application.InputBox(Prompt:= _
"Compare this workbook (" & wb1.Name & _
") to...?", _
Title:="Compare to what workbook?", _
Default:=wb2.Name, _
Type:=2)
If sBook = "False" Then Exit Sub
If Workbooks(sBook) Is Nothing Then
MsgBox "Workbook: " & sBook & " is not open."
GoTo ReDo1
Else
Set wb2 = Workbooks(sBook)
End If
'
Application.ScreenUpdating = False
For Each ws1 In wb1.Sheets
If Not wb2.Sheets(ws1.Name) Is Nothing Then
Set ws2 = wb2.Sheets(ws1.Name)
For Each Cell In ws1.UsedRange
If Cell.Formula <> ws2.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws2.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
If ws1.UsedRange.Rows.Count <> _
ws2.UsedRange.Rows.Count Or _
ws1.UsedRange.Columns.Count <> _
ws2.UsedRange.Columns.Count Then
For Each Cell In ws2.UsedRange
If Cell.Formula <> ws1.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws1.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
End If
End If
Next ws1
'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub Clear_Highlights_this_Sheet()
ActiveSheet.UsedRange. _
Interior.ColorIndex = xlNone
End Sub

Sub Clear_Highlights_All_Sheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.UsedRange.Interior.ColorIndex = xlNone
Next
End Sub
Answer
+11
moins plus
To find the range of Rows and Columns as a number -

ActiveSheet.UsedRange.Rows.Count
or
ActiveSheet.UsedRange.Columns.Count

You can store these in different variables, then use a For loop that goes from 1 - to this variable to make comparisons.

I hope this is what you were looking for.

Answer
+6
moins plus
Also in addition to to finding the number of Rows and Columns in the above way, if you are using the code you provided in the first place the syntax for autofill is as follows -

Selection.AutoFill Destination:=Range("A2", "EG" & var), Type:=xlFillDefault

where var is the variable that stores the row number or column number. This way you dont need the For loop.

This document entitled « macro to compare two excel sheets » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.