markpem
06-09-2015, 01:12 AM
Hello
I have some code below which works beautifully, by searching both worksheets and finding a matching reference number in Column A then copying all the non-blank data over from newdata to mainpage to make sure its up to date.
However, I would like a third sheet called LOGS (or similar) which would write what changes were made *IF* they are not the same in that cell (as it just copies all nonblank data). so some code like
If code from mainpage <> code from newdata then write to log: "Ref 12345k Changed from 'Pending' (<-- This would be the data in Mainpage) To 'Completed (<--This would be the data in Newdata)'"
Any help from some awesome experts out there?
Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long
Cancel = True ' Ignore error messages as first column is LOCKED
Sheets("MainPage").Select
With Sheets("NewData")
s2rw = 2 ' Adjust to first data row #
endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
s1rw = 0
On Error Resume Next
s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
On Error GoTo 0
If s1rw > 0 Then ' Found case #
For col = 2 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
End If
Next
End If
s2rw = s2rw + 1
Loop
.Select
End With
I have some code below which works beautifully, by searching both worksheets and finding a matching reference number in Column A then copying all the non-blank data over from newdata to mainpage to make sure its up to date.
However, I would like a third sheet called LOGS (or similar) which would write what changes were made *IF* they are not the same in that cell (as it just copies all nonblank data). so some code like
If code from mainpage <> code from newdata then write to log: "Ref 12345k Changed from 'Pending' (<-- This would be the data in Mainpage) To 'Completed (<--This would be the data in Newdata)'"
Any help from some awesome experts out there?
Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long
Cancel = True ' Ignore error messages as first column is LOCKED
Sheets("MainPage").Select
With Sheets("NewData")
s2rw = 2 ' Adjust to first data row #
endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
s1rw = 0
On Error Resume Next
s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
On Error GoTo 0
If s1rw > 0 Then ' Found case #
For col = 2 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
End If
Next
End If
s2rw = s2rw + 1
Loop
.Select
End With