Compare sheets and put missing rows into third sheet.
Closed
Biffzs
Posts
1
Registration date
Friday January 12, 2018
Status
Member
Last seen
January 13, 2018
-
Updated on Jan 15, 2018 at 06:53 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 15, 2018 at 06:46 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 15, 2018 at 06:46 AM
Related:
- Compare sheets and put missing rows into third sheet.
- Sheet right to left google sheets - Guide
- Windows network commands cheat sheet - Guide
- Little alchemy cheat sheet - Guide
- "Compare versions word" - Guide
- Beyond compare download - Download - File management
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jan 15, 2018 at 06:46 AM
Jan 15, 2018 at 06:46 AM
Hello Brian,
Try the following code in a standard module and assigned to a button:-
The code does as you would like. Codes for this sort of task can become quite involved so I've taken a little short cut and used some helper columns further over to the right (and out of the way) of Sheet1.
Following is the link to a little sample I've prepared for you. Click on the "RUN" button to see it work.
http://ge.tt/4rQCO7o2
I hope that this helps.
Cheerio,
vcoolio.
Try the following code in a standard module and assigned to a button:-
Sub Diffs()
Dim lr As Integer
Dim fValue As Range
Dim c As Range
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Sheet3.UsedRange.ClearContents
Sheet3.Rows(1).Value = Sheet1.Rows(1).Value
Sheet1.Range("A2:C" & lr).Copy Sheet1.[BA2]
For Each c In Sheet1.Range("A2:A" & lr)
Set fValue = Sheet2.Columns("A:A").Find(c.Value)
If fValue Is Nothing Then GoTo Nextc
If c.Value = fValue.Value Then
Range(c, c.Offset(0, 2)).ClearContents
End If
Nextc:
Next c
For Each c In Sheet1.Range("A2:A" & lr)
If c.Value <> "" Then
Sheet1.Range(Cells(c.Row, "A"), Cells(c.Row, "C")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
Next c
Sheet1.Range("BA2:BAC" & lr).Copy Sheet1.[A2]
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code does as you would like. Codes for this sort of task can become quite involved so I've taken a little short cut and used some helper columns further over to the right (and out of the way) of Sheet1.
Following is the link to a little sample I've prepared for you. Click on the "RUN" button to see it work.
http://ge.tt/4rQCO7o2
I hope that this helps.
Cheerio,
vcoolio.