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.
- Google sheet right to left - Guide
- Windows network commands cheat sheet - Guide
- Little alchemy cheat sheet - Guide
- Mark sheet in excel - Guide
- How to open excel sheet in notepad++ - Guide
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.