Vba code to extract data from one sheet to another

Hello all excel experts,

In sheet1 i have row data and sheet2 having output data actually i required a macro which should extract data from sheet1 to sheet2, in sheet2 column A(Name) data should extract from sheet1 from column A(Path) and date
in sheet2 column B(date) data should extract from sheet1 from column A(Path) and Mobile# in sheet2 column C(Mobile#) data should extract from sheet1 from column B(FileName).

Please find the attached sample file

Regards,

Xlhelp7

Could it be that in your exemple sheet 2 the date has to be 3/jan ?

Kind regards
Leo

This takes date from column A, that is different from column F

Sub test()
Dim lastrow As Long, x As Long, r As Integer, r2 As Integer, r3 As Integer
Dim mystring1 As String, mystring2 As String, mystring3 As String
With Sheets("sheet1").Range("A1").CurrentRegion
    .Copy Sheets("sheet2").Range("D1")
End With
With Sheets("sheet2")
    lastrow = .Range("D" & Rows.Count).End(xlUp).Row
    .Range("A1").Resize(, 3) = Array("Name", "Date", "Mobile#")
    .Range("D1", "D" & lastrow).Copy
    .Range("A1", "C" & lastrow).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    For x = 2 To lastrow
        r = InStr(4, .Range("D" & x), "\")
        r2 = InStr(r + 1, .Range("D" & x), "\")
        r3 = InStr(1, .Range("E" & x), "_")
        mystring1 = Mid(.Range("D" & x), r + 1, r2 - r - 1)
        mystring2 = Mid(.Range("D" & x), r2 + 1, Len(.Range("D" & x)) - (r2 + 1))
        mystring3 = Mid(.Range("E" & x), 1, r3 - 1)
        .Range("A" & x).Resize(, 3) = Array(mystring1, mystring2, "'" & mystring3)
    Next
    .Columns.AutoFit
End With
End Sub

Hi Leo,
Thank you for your reply - much appreciated.
Leo, that is exactly what I am looking for. Works a treat. Thank you!

Regards,

Xlhelp7