Date type changing when moving cells from one sheet to another

markpem

New member
Joined
Dec 15, 2014
Messages
2
Reaction score
0
Points
0
Hello All,

I have some code below that looks for any changes and finds the match on the other sheet and updates any changes, however the dates when transferred over go from the UK formatiing of dd/mm/yyyy to the US style date of mm/dd/yyyy making all the dates in the sheet incorrect.

Could someone help and advice how I make it stay the same way? Thanks!"

Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long

Sheets("Sheet1").Select
With Sheets("Sheet2")
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 = 1 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
Next
End If
Rows(s2rw).Delete
s2rw = s2rw + 1
Loop
.Select
End With
 
Back
Top