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
- Beyond compare - Download - File management
- Spell number in excel without macro - Guide
- Excel compare two sheets - Guide
- Macro excel download - Download - Spreadsheets
- Excel macro to create new sheet based on value in cells - 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