Compare 2 worksheets all 6 columns in each worksheet

[Bud]

Hello

Each week we produce a report of peoples time and bill this. It has 6 column
fields.

The person entering their time can go back and change their time(Hours) up
to 2 weeks back.

We can re-obtain the data and would like to run a compare against both
worksheets
and where there are differences copy that line in another worksheet and show
the difference for that whole row.
There are many resources but we expect very few changes...just trying to
identify them....

Ex.
Worksheet 1
Column A B C D E F
Pete Smith 8/29/2008 4 TaskA PRJ840 EN

Worksheet 2
Column A B C D E F
Pete Smith 8/29/2008 12 TaskA PRJ840 EN

What we would like it to do.....
Worksheet 3 Difference...
Column A B C D E F
Pete Smith 8/29/2008 8 TaskA PRJ840 EN

Thanks

 

[Rick Rothstein]

Is Column C the only column that can vary? I mean, what if the only change
is the date... the task... the project? How would those be reported (can't
show a subtraction of TaskE instead of TaskA)?

 

[Bud]

Nothing can change except the hours....This is a Timekeeping system called
SAP. The people can reselect the past two weeks transactions and the only
thing they can update on the screen itself is the hours. All other fields are
non-changeable on the screen including the date.

The data can than be reselected by going to the system and downloading it
into excel.

Just looking for a way to compare what changed and place the difference in
another worksheet.

We already saved what they originally did. We can re-obtain the data for
that week which would have the updated hours. We need to have a way to show
the difference automtically in another worksheet.

 

[Bud]

One other thing can happen and that is they may have forgotton to put in
their time for a Sunday and the m anager may have reminded them. That
employee would than select that task and enter hours. This transaction than
would not have been there before. This is why the matching would have to
occur on columns A,B,C, and D

 

[Rick Rothstein]

I think this macro will do what you want...

Sub UpdatedReport()
Dim X1 As Long
Dim X2 As Long
Dim X3 As Long
Dim RowVals As String
Dim WS(1 To 3) As Worksheet
Dim LastRow(1 To 3) As Long
For X1 = 1 To 3
Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _
",")(X1 - 1))
LastRow(X1) = WS(X1).Cells(WS(X1).Rows.Count, "A").End(xlUp).Row
Next
For X2 = 2 To LastRow(2)
With WS(2)
RowVals = .Cells(X2, "A").Value & .Cells(X2, "B").Value & _
.Cells(X2, "D").Value & .Cells(X2, "E").Value & _
.Cells(X2, "F").Value
For X1 = 2 To LastRow(1)
With WS(1)
If RowVals = .Cells(X1, "A").Value & .Cells(X1, "B").Value & _
.Cells(X1, "D").Value & .Cells(X1, "E").Value & _
.Cells(X1, "F").Value Then
.Rows(X1).Copy WS(3).Cells(LastRow(3) + 1, "A")
WS(3).Cells(LastRow(3) + 1, "C").Value = _
WS(2).Cells(X2, "C").Value - WS(1).Cells(X1, "C").Value
LastRow(3) = LastRow(3) + 1
Exit For
End If
If X1 = LastRow(1) Then
WS(2).Rows(X2).Copy WS(3).Cells(LastRow(3) + 1, "A")
End If
End With
Next
End With
Next
End Sub

 

[Bud]

I am getting a subscript out of range message on this
Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _

",")(X1 - 1))

I actually have data from old timesheet in a worksheet called SAP-OLD and
than the current timesheet data from that same time period as what is in the
SAP-OLD in a worksheet called SAP-NEW. I am hoping to compare the two since
they are different placing the differences in a worksheet called Diff

I have written some code in excel macro before but can't understand fully
what your code says.

Thanks

 

[Rick Rothstein]

Are your worksheets in fact named "Worksheet 1", "Worksheet 2" and
"Worksheet 3" as your original post seemed to indicate? If not, change those
pieces of text to your actual sheet names.

 

[Bud]

Hi Rick

This mainly works but it doesn't subtract accurately in all cases. There
were only 2 differences in both files but it showed more than that. Where
lines were identical it placed a difference in it.

I am not totally sure what each specific line of code is doing otherwise I
may be able to correct it.

Would it be possible for you to describe the code? ....or perhpas
understand why it would be doing that

Thanks