Why is Excel VBA changing the date format of my pasted text?

Question

I've recorded a macro of myself that seems to do something different when I re-run it compared to when I recorded it. My macro involves pasting a table of data into a sheet, with the table including some date and time cells. They're in the format "dd/mm/yyyy hh:mm:ss" When I recorded the macro, the date cells get changed to be formatted like "dd/mm/yyyy hh:mm".

When I run the macro, the data gets pasted and it swaps the values for date and month, formatting it as "mm/dd/yyyy hh:mm", swapping the month and date values. It wont format the data if the month date is 13 or more, as it isn't a valid month.

How can I stop VBA from inconsistently changing my data?

Here's an example of what happens to the date data:

Recording the macro,

01/05/2021 01:03:17 became 01/05/2021 01:03
14/05/2021 14:10:32 became 14/05/2021 14:10

Running the macro

01/05/2021 01:03:17 became 05/01/2021 01:03
14/05/2021 14:10:32 became 14/05/2021 14:10:32

Here's the macro I recorded:

    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("B4").Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False

score 0 · Answer 1 · answered Jun 04 '21 at 14:46

Change this line, i think becase you are change the format of the cell to text, therefore unexpected behaviour happen:

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False

To:

ActiveSheet.PasteSpecial Paste:=xlPasteValues

Why is Excel VBA changing the date format of my pasted text?

1 Answers1