VBA changing UK Date format to American

[mazzz]

Hi,

I have a macro which pastes a table of data from a 3rd party application. The first row of the table is date format (dd/mm/yyyy).

When I run the code the data is pasted however the date format is changed to American (mm/dd/yyyy). Is there a way to retain the UK date format when running the paste macro?

Simply this is my code:

Range("Paste_Cell").PasteSpecial xlPasteAll

Would appreciate any help with this. I've seen some answers regarding changing the region settings in control panel. I can confirm, my region settings are set to Engilsh(United Kingdom).

 

[MARK858]

Are you sure that is all your code? when I select A1 and then copy after I run the code

Sub xxxxx()
Range("Paste_Cell").PasteSpecial xlPasteAll
End Sub

I get the below (where D1 is Paste_Cell)

Book1.xlsb
	A	B	C	D	E	F	G	H	I	J
1	04/11/2023			04/11/2023					04 November 2023	04 November 2023
Sheet2

Cell Formulas
Range	Formula
I1	I1	=A1
J1	J1	=D1

Named Ranges
Name	Refers To	Cells
Paste_Cell	=Sheet2!$D$1	J1

 

[mazzz]

I've simplified my code to just the line which is responsible for pasting. The data is copied onto the clipboard from a 3rd party application. Then the paste macro is run to dump the data in.

The first column of the data copied is date format (dd/mm/yyyy), however when it's pasted it is being converted to (mm/dd/yyyy).

for example:

Copy this date from here (dd/mm/yyyy): 11/06/2023
*Date has to be copied from outside excel, there isn't a problem when copying a date in excel and pasting it in excel*

the run this code in excel....

Range("A1").PasteSpecial xlPasteAll

The date format is changed to 06/11/2023 as in 6th of November...

If you paste manually (Ctrl + V) it works fine, you get 11/06/2023.

 

[MARK858]

Is it an option to select the Paste_Cell range in the code?

 

[MARK858]

Sorry that won't work either, I'll have a think about getting it from the clipboard. If you were bringing it into vba from a sheet we would use CDate or Clng but that isn't an option from the clipboard

 

[MARK858]

Ok, a bit long winded and I am sure there are better ways but try the code below
You must set a reference to the Microsoft Forms 2.0 Object Library

Sub mazzz()
    Dim CObj As MSForms.DataObject, XText As String

    Set CObj = New MSForms.DataObject

    CObj.GetFromClipboard
    XText = CDate(CObj.GetText(1))
    Range("Paste_cell") = DateValue(XText)
End Sub

 

[mazzz]

Mark858, i'm not sure if youre solution would work as the data I am copying has date, values, and text. Its only the first row of the table that is pasted that has the dates...

So, I've spent another couple of hours on it and I've found a solution although its slowed down the execution of the macro considerably 10+ seconds!

This works...
' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

Dim DateRange As Range

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell in DateRange
Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

However because this is having to cycle through 3000 cells its slowed the paste macro from 1-2 seconds to over 10seconds!

I've tried to use:
Range("A2:A3000").NumberFormat = "dd/mm/yyyy"
but this doesn't work.

Using text to columns manually does the trick nicely and quickly, however I haven't been able to integrate that into my VBA code and get the macro working. Unfortunately doing the text to columns method manually isn't a viable option for the application.

 

[Alex Blakenburg]

Give this a try:

    Dim DateRange As Range
    
    Range("A1").PasteSpecial xlPasteAll
    
    ' USD to UK/AU date reformat
    Dim LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set DateRange = Range("A2:A" & LastRow)
    
    DateRange.TextToColumns Destination:=DateRange.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True

 

[mazzz]

Give this a try:

    Dim DateRange As Range
  
    Range("A1").PasteSpecial xlPasteAll
  
    ' USD to UK/AU date reformat
    Dim LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set DateRange = Range("A2:A" & LastRow)
    
    DateRange.TextToColumns Destination:=DateRange.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True

 

[rlv01]

You say that this works, but takes a long time:

' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

    Dim DateRange As Range

    Range("A1").PasteSpecial xlPasteAll
    Set DateRange = Range("A2:A3000")
    For Each Cell In DateRange
        Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
    Next Cell

If so, then you should be able to simplify to:

' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

    Dim DateRange As Range

    Range("A1").PasteSpecial xlPasteAll
    Set DateRange = Range("A2:A3000")
    DateRange.NumberFormat = "dd/mm/yyyy"

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)