My suggestion worked in my testing.

I'm surprised that your code copied the formatting. I would have guessed excel
saw you were entering dates and just used its short date format--not the format
from the "sending" cells.

" wrote:

Dave I tried your example and it didnt work, same result.

Another tidbit is that if I use:

ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "3").Value =
Format(Sheets("Settings").Range(TempWeekRange + "10").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "4").Value =
Format(Sheets("Settings").Range(TempWeekRange + "11").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "5").Value =
Format(Sheets("Settings").Range(TempWeekRange + "12").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "6").Value =
Format(Sheets("Settings").Range(TempWeekRange + "13").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "7").Value =
Format(Sheets("Settings").Range(TempWeekRange + "14").Value, "dd/mmm/
yyyy")
ThisWorkbook.Sheets("Settings").Range(TempWeekRang e + "8").Value =
Format(Sheets("Settings").Range(TempWeekRange + "15").Value, "dd/mmm/
yyyy")

then it copies it properly with correct format.
The problem with it is that if there are other workbooks opened then
this is slow as mollasses and literally takes seconds to complete.
If it it the only workbook opened then it is fast.
Thats why I dont want to use this approach.

On Jul 13, 8:50 am, Dave Peterson wrote:
Sheets don't have to be visible or even active to copy or paste.

" wrote:

Another problem for the Copy and Paste solution is that Sheet Settings
is a hidden sheet and is never the Activesheet.

--

Dave Peterson

--

Dave Peterson