Compare two sheets in One Column Copy Particular row if different.

T

Tamiz1982

New Member
Joined
Sep 11, 2020
Messages
17
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I am comparing two sheets "Online" & "Manual".
In "Manual" Sheet column (Total Attendance) compared to "Online" sheet .
If the column of cell value changed in "Manual" sheet that particular row should copy to another sheet "Attend_Edit" end of the row...
Attend_Edit.jpg
Attend_Edit-2.jpg
Attend_Edit-3.jpg

Kindly help....
Thank You..
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi,

Please use below code.

VBA Code: 
Sub compareCopy()
   Dim manualRows As Integer, onlineRows As Integer, destRows As Integer
   Dim onlineAttendance As Integer, rowno As Integer
   
   manualRows = Sheets("Manual").Cells(Rows.Count, 1).End(xlUp).Row
   onlineRows = Sheets("Online").Cells(Rows.Count, 2).End(xlUp).Row
   
   For rowno = 2 To manualRows
   destRows = Sheets("Attend_Edit").Cells(Rows.Count, 1).End(xlUp).Row + 1
   onlineAttendance = WorksheetFunction.VLookup(Sheets("Manual").Range("A" & rowno), Sheets("Online").Range("C:D"), 2, 0)
    If Sheets("Manual").Range("C" & rowno) <> onlineAttendance Then
        Sheets("Manual").Range("A" & rowno & ":" & "C" & rowno).Copy Sheets("Attend_Edit").Range("A" & destRows)
    End If
   Next
End Sub
 
Upvote 0
Solution
Saurabhj said:
Hi,

Please use below code.

VBA Code: 
Sub compareCopy()
   Dim manualRows As Integer, onlineRows As Integer, destRows As Integer
   Dim onlineAttendance As Integer, rowno As Integer
 
   manualRows = Sheets("Manual").Cells(Rows.Count, 1).End(xlUp).Row
   onlineRows = Sheets("Online").Cells(Rows.Count, 2).End(xlUp).Row
 
   For rowno = 2 To manualRows
   destRows = Sheets("Attend_Edit").Cells(Rows.Count, 1).End(xlUp).Row + 1
   onlineAttendance = WorksheetFunction.VLookup(Sheets("Manual").Range("A" & rowno), Sheets("Online").Range("C:D"), 2, 0)
    If Sheets("Manual").Range("C" & rowno) <> onlineAttendance Then
        Sheets("Manual").Range("A" & rowno & ":" & "C" & rowno).Copy Sheets("Attend_Edit").Range("A" & destRows)
    End If
   Next
End Sub
Click to expand...
Thank You very much.... I get exact result after removed link between "manual" & "Online " sheets. Once again Thank You for spending me your own time
 
Last edited:
Upvote 0
Great. thanks for the feedback. Please mark the post as Solution.
 
Upvote 0
Hi
You have already answered this my post. Now i want to simplify the code...
I tried a small project..
1. "New" Button --> Clear the data in "Manual" & "Online" Sheet. ' Its working fine.
2. "Sort JobCard No" --> Sort the "Online" Sheet & transfer data to "Manual" sheet. 'Its working good.
3. " Convert CSV" --> Before press this button I do manually changed the attendance no.of days. After click this button compare both sheet which is different row copy to another sheet. "Attend_Edit". Also I do copy another work book "Attend_Edit.csv".
* I want copy row values only... without formulas.
* two time i do copy & paste file. I just want copy & paste records to "Attend_Edit.csv".
Attend_Edit_Manual.xlsm
ABCDEFGHIJKLMN
1Registration No.NameTotal AttendanceDay-1Day-2Day-3Day-4Day-5Day-6Day-7Work CodeMuster_RollPt. CodeSearch Work
2TN-06-015-005-007/862-AShanthi6YESYESYESNOYESYESYES2906015005/WC/2904726916314192906015005726916
3TN-06-015-005-007/885-ARajeshwari6YESYESYESNOYESYESYES2906015005/WC/2904726916314192906015005726916
4TN-06-015-005-007/903-ASasikumar4YESYESYESNOYESNONO2906015005/WC/2904726916314192906015005726916
5TN-06-015-005-007/908Ravichandiran6YESYESYESNOYESYESYES2906015005/WC/2904726916314192906015005726916
6TN-06-015-005-007/943-APonnammal6YESYESYESNOYESYESYES2906015005/WC/2904726916314192906015005726916
7TN-06-015-005-007/969-AKumar6YESYESYESNOYESYESYES2906015005/WC/2904726916314192906015005726916
8TN-06-015-005-007/991-AKavitha6YESYESYESNOYESYESYES2906015005/WC/2904726916314192906015005726916
9NONONONONONONO2906015005/WC/2904726916314192906015005726916
10NONONONONONONO2906015005/WC/2904726916314192906015005726916
11NONONONONONONO2906015005/WC/2904726916314192906015005726916
12NONONONONONONO2906015005/WC/2904726916314192906015005726916
13NONONONONONONO2906015005/WC/2904726916314192906015005726916
14NONONONONONONO2906015005/WC/2904726916314192906015005726916
15NONONONONONONO2906015005/WC/2904726916314192906015005726916
16NONONONONONONO2906015005/WC/2904726916314192906015005726916
17NONONONONONONO2906015005/WC/2904726916314192906015005726916
18NONONONONONONO2906015005/WC/2904726916314192906015005726916
19NONONONONONONO2906015005/WC/2904726916314192906015005726916
20NONONONONONONO2906015005/WC/2904726916314192906015005726916
21NONONONONONONO2906015005/WC/2904726916314192906015005726916
22Total Mandays40
Manual
Cell Formulas
RangeFormula
D2:D21D2=IF($C2>=1,"YES","NO")
E2:E21E2=IF($C2>=2,"YES","NO")
F2:F21F2=IF($C2>=3,"YES","NO")
G2:G21G2=IF(AND($C2>3,$C2<4),"YES","NO")
H2:H21H2=IF($C2>=4,"YES","NO")
I2:I21I2=IF($C2>=5,"YES","NO")
J2:J21J2=IF($C2>=6,"YES","NO")
M2M2=LEFT(K2,10)
N2N2=RIGHT(K2,6)
K3:N21K3=K2
C22C22=SUM(C2:C21)
 

Attachments

  • Manul.jpg
    Manul.jpg
    183.7 KB · Views: 5
  • Online.jpg
    Online.jpg
    140.6 KB · Views: 5
Upvote 0
Hi, to copy and paste as value you can use below code:

VBA Code: 
Sub copyValues()
Sheets("Sheet1").Range("A1:B100").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub
 
Upvote 0
VBA Code: 
Sub compareCopy()
   Dim manualRows As Integer, onlineRows As Integer, destRows As Integer
   Dim onlineAttendance As Integer, rowno As Integer
  
   manualRows = Sheets("Manual").Cells(Rows.Count, 1).End(xlUp).Row
   onlineRows = Sheets("Online").Cells(Rows.Count, 2).End(xlUp).Row
  
   For rowno = 2 To manualRows
   destRows = Sheets("Attend_Edit").Cells(Rows.Count, 1).End(xlUp).Row + 1
   onlineAttendance = WorksheetFunction.VLookup(Sheets("Manual").Range("A" & rowno), Sheets("Online").Range("C:D"), 2, 0)
    If Sheets("Manual").Range("C" & rowno) <> onlineAttendance Then
        Sheets("Manual").Range("A" & rowno & ":" & "C" & rowno).Copy Sheets("Attend_Edit").Range("A" & destRows)
    End If
   Next
End Sub
Where i have to apply "PasteSpecial Paste:=xlPasteValues"
 
Upvote 0
VBA Code: 
 Sheets("Attend_Edit").Range("A" & destRows).PasteSpecial Paste:=xlPasteValues
Thanks a lot....
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Excel VBA When new rows are added to table 1 copy the information to table 2
Replies
0
Views
400
BlueHawker42
B
T
  • Question
Compare spreadsheet/worksheet with changing rows
Replies
2
Views
74
BenW71
B
E
  • Solved
This one is tricky. VLookup but return a different cell value
Replies
6
Views
128
Alex Blakenburg
A
F
  • Question
How to compare data between two sheets, extract the changed lines and copy into another sheet
Replies
7
Views
261
frostworks
F
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
10
Views
575
kevin9999
K

Forum statistics

Threads
1,214,575
Messages
6,120,344
Members
448,956
Latest member
Adamsxl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top