stopping Date format changing on copy and paste!

asylum

Board Regular
Joined
Dec 2, 2003
Messages
243
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Forum statistics

Threads
1,141,847
Messages
5,708,959
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top