Date problem

[vmjan02]

I have a csv file were the Date format is correct DD-MM-YYYY (ref screen CopyPaste Date), once i copy and paste this data to excel vba file in a sheet the last year date gets convered to 17/06/22 (ref screen RawFile Date) which is not correct copy and paste

to avoid this I have put the formula as in D9 so that all the calculaionts should work
=IFERROR(DATEVALUE(E9),VALUE(TEXT(E9,"DD-MM-YYYY")))

But it is not working as required, any help on this.

Scrip below is for copy and paste

'=====================================================================================
    ' 1. ImportDaily Fiber_OLT_Volume_Makkah_report
'=====================================================================================
sub copypaste()
fname = Dir(Fpath & "\" & "1 Fiber Makkah report.csv")
       
    If Sheets("Reference").Range("b2").Value = "1 Fiber Makkah report" Then
    
    Sheets("Fiber RawData").Visible = True
    Sheets("Fiber RawData").Unprotect "etmc123$"

    Windows("Email Body TP-Fixed Access Hajj Report 1444 - V1.xlsm").Activate
    ActiveWorkbook.Sheets("Fiber RawData").Activate
    ActiveSheet.Range("E8:N5000").ClearContents
    'Cells.Select
    'Selection.ClearContents
    Application.CutCopyMode = False
        
    Set wbsource = Workbooks.Open(Fpath & "\" & fname)
           
    Windows(fname).Activate
        Range("B1:I500").Select
        'Cells.Select
        Selection.Copy
       Windows("Email Body TP-Fixed Access Hajj Report 1444 - V1.xlsm").Activate
        ActiveWorkbook.Sheets("Fiber RawData").Activate
        ActiveSheet.Range("E8").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    wbsource.Close
    End If


End Sub

 

[Herakles]

I've taken the liberty to rewrite the code to avoid having to activate, select and copy data.

The code takes the values from the CSV file rather than copy and paste the data.

I have not got your data but this works for me.

I have assumed that Fpath is assigned a value outside of this procedure.

'=====================================================================================
    ' 1. ImportDaily Fiber_OLT_Volume_Makkah_report
'=====================================================================================Sub copypaste()
Dim WbSource As Workbook
Dim fname As String
Dim WbMain As Workbook

    Set WbMain = Workbooks("Email Body TP-Fixed Access Hajj Report 1444 - V1.xlsm")
    
    fname = Dir(Fpath & "\" & "1 Fiber Makkah report.csv")
       
    If WbMain.Sheets("Reference").Range("B2").Value <> "1 Fiber Makkah report" Then
        Exit Sub
    End If
            
    Set WbSource = Workbooks.Open(Fpath & "\" & fname)
    
    WbMain.Activate
    
    With WbMain
        .Activate
        With .Worksheets("Fiber RawData")
            .Unprotect "etmc123$"
            .Visible = True
            .Activate
            .Range("E8:N5000").ClearContents
            .Range("E9:F507").NumberFormat = "DD-MM-YYYY"
            .Range("E8:L507").Value = WbSource.Sheets(1).Range("B1:I500").Value
        End With
        .Save
    End With
    
    WbSource.Close

End Sub

 

[vmjan02]

kool, Harakles
thanks this is working perfect as expected

thanks for your support, sorry for my late response was busy with other task.

​