Importing Multiple Files to Single Workbook date issue

Hi all,

I am having an issue with a macro I am writing and can't get to the bottom of it.

The aim is to import multiple files in a folder into a single workbook. These files are tab delimited files.

The issue is, that the first column of all files is a date and time.
When I import and data using the below macro, the date and time gets all messed up.
The raw date and time is in UK format but when I import all the files to the single workbook, some of the dates get changed to US format and some don't.
I want all the dates to be in UK format as that of the raw data.

Any ideas what the issue might be?

Any help would be most appreciated.

Thanks in advance.

_________________________________

Sub ImportAllFiles()
      
    Dim Path            As String
    Dim Filename        As String
    Dim WkB             As Workbook
    Dim Ws              As Worksheet
      
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = ActiveWorkbook.Path
    Filename = Dir(Path & "\*.", vbNormal)
    Do Until Filename = ""
        If Filename <> ThisWorkbook.Name Then
        Set WkB = Workbooks.Open(Filename:=Path & "\" & Filename, Format:=1)
        For Each Ws In WkB.Worksheets
            Ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Next Ws
        WkB.Close False
        Filename = Dir()
        Else: Filename = ""
        End If
    Loop

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Did you try pastespecial (xlpastevaluesandnumberformats) ?

Instead of doing a worksheet.copy, you can try copying the contents of the worksheet. That way, you can use a pastespecial command.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

I will do it for you this time.