Macro filling UK Date that is a value as a US date in column with same format as source

I have a file in named range 'caldata3' with a date saved as a value with the format

Date
14/03/2012
English (United Kingdom)

If the date in question is 07/01/2021 my macro fills this data to another file but it appears as a US date of 01/07/2021. The target column has the same format as above.
Here is my code.

Sub logsievecalibration()
'
Dim ThisWorkBook As String
Dim SheetName As String
Dim MyRanges(13) As String
Dim EmptyRow As Long
Dim a As Integer 'to cyle through ranges


' Unprotect sheet to allow macro to function
 Call UnProtectSheets
    
Worksheets("Sieve Details & Results").Activate
 
ThisWorkBook = ActiveWorkbook.Name
SheetName = ActiveSheet.Name
 
'Named Ranges with sheets specified as from more than one sheet

MyRanges(1) = "caldata1" 'Certificate Type - col B
MyRanges(2) = "caldata2" 'Certificate Number - col C
MyRanges(3) = "caldata3" 'Calibration Date - col D
MyRanges(4) = "caldata4" 'Customer Account Number - col D
MyRanges(5) = "caldata5" 'Customer Name - col E
MyRanges(6) = "caldata6" 'Col5 from Opera records should be Town - col F
MyRanges(7) = "caldata7" 'Postcode column from Opera records should be Postcode - col G
MyRanges(8) = "caldata8" 'Standard - col H
MyRanges(9) = "caldata9" 'Diameter - col I
MyRanges(10) = "caldata10" 'Aperture - col K
MyRanges(11) = "caldata11" 'Unique Identifier - col L
MyRanges(12) = "caldata12" 'Email Address - col L
MyRanges(13) = "caldata13" 'Job Number - col M

'   Assign values from range names to array
   MyRanges(1) = Range("caldata1").Value
   MyRanges(2) = Range("caldata2").Value2
   MyRanges(3) = Range("caldata3").Value
   MyRanges(4) = Range("caldata4").Value
   MyRanges(5) = Range("caldata5").Value
   MyRanges(6) = Range("caldata6").Value
   MyRanges(7) = Range("caldata7").Value
   MyRanges(8) = Range("caldata8").Value
   MyRanges(9) = Range("caldata9").Value
   MyRanges(10) = Range("caldata10").Value
   MyRanges(11) = Range("caldata11").Value
   MyRanges(12) = Range("caldata12").Value
   MyRanges(13) = Range("caldata13").Value
   
   
   
    Workbooks.Open filename:= _
        "S:\Calibrations\Ledgers\Sieve_Ledger_Latest.xls"
    Workbooks("Sieve_Ledger_Latest.xls").Activate
        
With Workbooks("Sieve_Ledger_Latest.xls")
    .Sheets("Calibrations").Activate
     
    With ActiveSheet
         
         'find empty row
        EmptyRow = 0
        Do
            EmptyRow = EmptyRow + 1
        Loop Until IsEmpty(.Cells(EmptyRow, 1))
        
         .Cells(EmptyRow, 1) = Date
        
         'fill in other columns from named ranges
                 
        For a = 1 To UBound(MyRanges)
         .Cells(EmptyRow, a + 1) = MyRanges(a)
        Next a
        
    End With
     
    
     'save and close workbook
    .Save
    .Close
End With

I posted this query in the Microsoft forum but the only suggestion was;

    'Save today's date as value
    Range("caldata3") =  Format(Date, "dd/mm/yyyy")

This is no good as it changes the date to the current one and I need to keep the original date.

I am stumped and would be most grateful for some help with this, thanks.

Originally Posted by AlphaFrog

This is a guess, but give it a shot...

MyRanges(3) = DateValue(Range("caldata3").Value)

The DateValue function converts a Text-Date into a Serial-Date using your computer's system date setting e.g. English (United Kingdom) d/m/yyyy

Thanks for trying AlphaFrog.
Unfortunately source date of 07/01/21 saved to the ledger as 01/07/21.

Originally Posted by rorya

Don't define MyRanges as a String. Define it as Variant so you can put an actual date value in there.

Thank you rorya. You have saved me losing sleep over the weekend.
I like your dog too.
Many many thanks