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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

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