Dim lastrow_sheet123 As Long
' Get how many rows of data have been loaded into the sheet.
lastrow_sheet123 = .Cells(Rows.Count, 4).End(xlUp).Row
' Prevent line 2 being deleted - as this contains the formulae which need coping down later.
If lastrow_sheet123 < 3 Then
lastrow_sheet123 = 3
End If
' Convert the date using a formula.
' In order to convert the date it is necessary to use a formula that copies the data from the input file instead of copy/pasting it.
.Range("C2").Formula = "=if('DIVI - Auto'!J2>9999999,DATE(RIGHT('Sheet123'!J2,4),MID('Sheet123'!J2,3,2),LEFT('Sheet123'!J2,2)),DATE(RIGHT('Sheet123'!J2,4),MID('DIVI - Auto'!J2,2,2),LEFT('Sheet123'!J2,1)))"
' Change format of date.
.Range("C2:C" & lastrow_sheet123).NumberFormat = "yyyymmdd"
.Range("C2:C" & lastrow_sheet123).Calculate