stopping Date format changing on copy and paste!

[asylum]

HI all,

I have a most peculiar thing happening which i can't fathom out - bleow is a portion of code from a vba macro i have written. you can see it opens a file, and copies its contents into another page in another file.


Workbooks.Open strPath & "\Query1.XLS"

Range("A3").Select
Range("A3:H5002").Select
Selection.Copy

Windows("Lincolnshire NSIR Audit Sanitised.xls").Activate
Sheets("Data").Visible = True

Sheets("DATA").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False

Windows("Query1.XLS").Activate
ActiveWindow.Close


Easy peasy so far right.....

Well one of the columns of data it copies is a date column, which is in uk format (dd/mm/yy) ie 9th feb 06 is 09/02/06. when it is pasted into the new file the format is the same (dd/mm/yy) however for some bizzarr reason the day and month figures have changed position, so read 02/09/06, still with the format (when checked) dd/mm/yy

I have absolutly no clue whythis is happening, it should not! help! ta

Andy

 

[Derek]

Hi there

No idea why this might be happening, but see if code like this helps. The sheet DATA can remain hidden. (It assumes data is coming from sheet named Sheet1 - change to your sheet's name.

Sub TEST()
Sheets("DATA").Range("A2:H501").Value = Sheets("Sheet1").Range("A3:H502").Value
End Sub

regards
Derek