VBA CopyPaste changing date format.

BMAK

New Member
Joined
Nov 26, 2008
Messages
8
I have a macro i use to copy data from one file to another the code is shown below. The data contains a column with the date in the format of dd/mm/yyyy, when i copy and paste the data to the new sheet the date is changed to mm/dd/yyyy, does anyone know why this is happening and any ideas for resolving it?


Code:
Sub CopyNewDate()
    Dim wbSource As Workbook, wbTarget As Workbook
    Dim rCopy  As Range
    Dim tPath  As String
    Dim RangeSource As Range
    Dim Filldata As Range
    Dim X As Variant
    Dim LastRow As Long
 
 
'***************************************************************************************
' Copy Range from A5 to next blank from source data in selected file
'***************************************************************************************
 
    Application.DisplayAlerts = False
     With Application
        .ScreenUpdating = False
        On Error Resume Next
         ' open the source workbook as ReadOnly
        Set wbSource = Workbooks.Open("C:\DATA\Example.xls", False, True)
        LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        wbSource.Worksheets("Example").Range("A5", "O" & LastRow).Copy
        wbSource.Close False ' close the source workbook without  saving  changes
        Application.GoTo Worksheets("DATA").Range("C65536").End(xlUp).Offset(1, 0), True
        ActiveSheet.PasteSpecial
        Set wbSource = Nothing
        Set wbTarget = Nothing
        Set rCopy = Nothing
        .ScreenUpdating = True
        .CutCopyMode = False 'clear Clipboard
        End With
 
'***************************************************************************************
' Unmerge all cells and fill data down
'***************************************************************************************
        Sheets("DATA").Select ' Selects range to fill data down
       Set Filldata = Range(E1, Cells(Rows.Count, 5).End(xlUp))
            Filldata.Select
            Filldata.UnMerge
            For Each X In Selection.Cells
                If X.Text = "" Then
                X.Value = X.Offset(-1, 0).Value
            End If
             Next X ' IF statement fills each cell until new value then fills that value for entire range.
 
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,214,929
Messages
6,122,315
Members
449,081
Latest member
tanurai

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
Back
Top