Compare two worksheets macro
Closed
dhunter
rizvisa1
- Posts
- 6
- Registration date
- Wednesday December 15, 2010
- Status
- Member
- Last seen
- January 2, 2011
rizvisa1
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Related:
- Compare two worksheets macro
- Compare two worksheets and paste differences to another sheet - excel vba free download ✓ - Forum - Excel
- How to compare two Excel sheets with varying data ✓ - Forum - Excel
- Macro to compare 2 sheets and copy differences ✓ - Forum - Excel
- VBA Compare 2 sheets and output difference to 3rd sheet - Forum - Excel
- Macro to compare two excel sheets ✓ - Forum - Excel
1 reply
rizvisa1
Feb 9, 2011 at 04:57 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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