Compare two worksheets macro
Closed
dhunter
Posts
6
Registration date
Wednesday December 15, 2010
Status
Member
Last seen
January 2, 2011
-
Dec 29, 2010 at 11:34 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 9, 2011 at 04:57 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 9, 2011 at 04:57 PM
Related:
- Compare two worksheets macro
- "Compare versions word" - Guide
- Beyond compare download - Download - File management
- How to Compare two workbooks and see if data has moved or not? - Excel Forum
- Excel online macro - Guide
- Excel run macro on open - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 9, 2011 at 04:57 PM
Feb 9, 2011 at 04:57 PM
Sub Compare()
'declaration of three variable of type range
Dim rng As Range, c As Range, cfind As Range
'in case of error just execute the next line
On Error Resume Next
'clear all the cells on the worksheet named "Confirmed"
Worksheets("Confirmed").Cells.Clear
' a short cut , so that one does not have to write Worksheets("testing") every time
With Worksheets("testing")
'in the line below .Range("a2").End(xlDown) will find the first empty cell after A2 and give u
' one cell above it. HOWEVER If all cells after A2 are blank, then it means it will give you the
' last cell of the column
'set the variable rng to be equal to range A2 till the last cell as determined by .Range("a2").End(xlDown)
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
' looping true each cells in the range in the variable rng
' c will hold the value of the cell
For Each c In rng
' now refering to Worksheets("sourcedata") for shortcut
With Worksheets("sourcedata")
'find in column A or sourcedata sheet, the value of the cell being looped.
'xlWhole is saying the match needs to be complete (though not case sensitive)
Set cfind = .Columns("A:A").Cells.Find _
(what:=c.Value, lookat:=xlWhole)
' if no match was found, then goto label "line1"
If cfind Is Nothing Then GoTo line1
'copy the entire row of the cell being looped and paste on Worksheets("Confirmed")
' on the first blank line from the bottom
c.EntireRow.Copy Worksheets("Confirmed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' this line has been commented out
'c.Copy Worksheets("Confirmed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' this line has been commented out
'c.Offset(0, 2).Copy Worksheets("Confirmed").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
' end of short cut for sourcedata
End With 'sourcedata
'a label named line1
line1:
' loop to next cell in range rng
Next c
'clear out the clipboard to free up memory
Application.CutCopyMode = False
'end of short cut for "testing"
End With '"testing"
End Sub