Auto Change Date format from "." to "/" when copy data from SAP T-code - VF05

I needed to copy data from VF05 SAP T-Code, in which billing date is in "dd.mm.yyyy" format to my worksheet VF05_UTILITY. I needed the billing date to auto convert into "dd/mm/yyyy" as soon as I paste data from sap vf05.
Please refer the image and file attached.

Please Login or Register  to view this content.

This code also converting . to / from the Net Value column when pasting data which I don't want. I only need . to / conversion in billing date (column L) of my worksheet.

try

Please Login or Register  to view this content.

Try this code under a button

it tests if L3 is a date or not. if not it will convert the whole column with text to column functionallity

Please Login or Register  to view this content.

This code is in Module1.
I do not use an event code in this case as it only used once after opening
if you use a selection change event it will trigger the code everytime you change a cell.


You can call this macro with a button or from the macros menu under Developer Ribbonmenu.

see attachment

Originally Posted by nigelog

try

Please Login or Register  to view this content.

Sir,

This code is converting my billing date "dd.mm.yyy" into "mm/dd/yyyy" format instead of "dd/mm/yyyy" format.

Originally Posted by Roel Jongman

Try this code under a button

it tests if L3 is a date or not. if not it will convert the whole column with text to column functionallity

Please Login or Register  to view this content.

This code is in Module1.
I do not use an event code in this case as it only used once after opening
if you use a selection change event it will trigger the code everytime you change a cell.


You can call this macro with a button or from the macros menu under Developer Ribbonmenu.

see attachment

This is working but can I have this in my sheet event code as private sub.

OK, that is possible too..

paste this code into the code module Sheet1(VGF05_UTILITY) then

Please Login or Register  to view this content.

small request: please do not use quotes in every reply, it only clutters the topics.

@nigelog sir,

I think we need to use CDate function in the code because it is converting into US date format "mm/dd/yyyy" instead of "dd/mm/yyyy" format.

sorry cannot help you then.. it is has become unclear what you need

Sir,

Your code is working only when cell L3 is not as Date. But I needed code to change all the cells in column L which is not as in date format.
I used to copy paste data from sap to my worksheet of many dealers one by one.
Because of this when I paste data of my first dealer in range for example J3:S19, the code work fine but later when I copy paste data of second dealer in range for example J20 to S30 the code is not working for this range as the cell L3 has been already converted into date.

Please help sir.

This seems to work continually


in worksheet Module

Please Login or Register  to view this content.

in module

Please Login or Register  to view this content.