Copy+Paste macro dd/mm/yyyy to dd/mm/yyyy and NOT dd/mm/yyyy to mm/dd/yyyy

**uimhirADo** · 08-02-2012, 11:50 PM

I have dates(dd/mm/yyyy) going across A4 to GA4 in a table in a .xlsm workbook. This table feeds a pivot chart and pivot table on another sheet.

Now I have a macro that opens a .csv file from another directory and selects data to paste in on the table. this data has (dd/mm/yyyy) going across A4 to GA4.

But when I paste the data in the dates are pasted in as mm/dd/yyyy.

Now I have tried stuff like this

Please Login or Register  to view this content.

But I cannot get it to paste the dates as dd/mm/yyyy

Any ideas??

**macropod** · 08-03-2012, 02:17 AM

The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the form of date values that you can format as dates via Format|Cells|Number|Date.

Please Login or Register  to view this content.

Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.

**uimhirADo** · 08-06-2012, 07:54 PM

Hi macropod thanks for that. I had to make the below changes to get it to work for me exactly as I wanted so thanks again. (It basically goes through each cell from D10 to GA10 and changes the cell from mm/dd/yyyy to dd/mm/yyyy)

Please Login or Register  to view this content.

However, I would still like to not have to do this as this slows my macro down. I will endeavour to find a better way

I also don't fully understand this code but I will play around with it to get a better understanding. But If you could answer me this. What is the below code for? Or is it not effective since the changes I made to it?

Please Login or Register  to view this content.

**macropod** · 08-08-2012, 03:45 AM

The code you asked about is designed to differentiate between whether the macro should apply to just one cell or a selection, rather than blindly applying itself to the entire worksheet. Given that you have "Set DtRange = Range("d10:ga10")", you can delete those lines.

You can speed things up considerably by switching off screen updating and, if other cells etc are dependent on the cells being processed, switching of recalculation until the processing is done.