Try formatting your data within the VBA code instead of formatting the data
in the worksheet. Something like:

strRenDate = Format(Cells(RowDates, "E").Value, "dd/mm/yyyy")

HTH
Mike.

--
Michael J. Malinsky


"andycharger " wrote in message
...
Hi,
Im writing a macro and im storing a date value form my spreadsheet in a
variable.

I had been doing this:

Dim strRenDate as String

strRenDate = Cells(RowDates, "E").Value

Now this worked ok EXCEPT when it pastes the date into another
spreadsheet, It seems to confuse the date and put it into an American
format (i.e instead of 08/01/2004 it says 01/08/2004)
This is despite me formatting both spreadsheet columns to read the date
as dd/mm/yyyy.

I guessed the best way to do it would be to store the variable as a
DATE rather than a string.

However, When I tried this, It said Type Mismatch.

Any idea how I get the value from my cell into a DATE style and write
it into another cell without this date reversing issue?

Thanks


---
Message posted from http://www.ExcelForum.com/