Extract multiple values based upon pattern to new worksheet including including adjacent column data

[stefjas]

HI,

I would like to know if it is possible to extract data based upon a pattern from a text cell with adjacent column data towards a new worksheet.
Used version Excel : Excel 2013

Origin/Source ( 4 rows )

[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Region[/TD]
[TD]Reports Generated[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]East[/TD]
[TD]This reports were generated : H:\common_place\reports\2015_ABCDrep001.xls for march 2015 = H:\common_place\reports\2015_XYZArep002.xls for 2015/08/08 H:\common_place\reports\2015_ZWYXrep001.xls[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]West[/TD]
[TD]This reports were generated : H:\common_place\reports\2015_ABCDrep123.xls H:\common_place\reports\2015_ZWYXrep421.xlsx[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]North[/TD]
[TD]No reports were generated, but maybe in near future[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Center[/TD]
[TD]Ipsum H:\common_place\reports\2015_ABCDrep005.xls Once generated H:\common_place\reports\2015_XYZArep009.xlsx but with comments[/TD]
[/TR]
</tbody>[/TABLE]

Output ( 7 rows )

[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Region[/TD]
[TD]Reports[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]East[/TD]
[TD]H:\common_place\reports\2015_ABCDrep001.xls[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]East[/TD]
[TD]H:\common_place\reports\2015_XYZArep002.xls[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]East[/TD]
[TD]H:\common_place\reports\2015_ZWYXrep001.xls[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]West[/TD]
[TD]H:\common_place\reports\2015_ABCDrep123.xls[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]West[/TD]
[TD]H:\common_place\reports\2015_ZWYXrep421.xlsx[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Center[/TD]
[TD]H:\common_place\reports\2015_ABCDrep005.xls[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Center[/TD]
[TD]H:\common_place\reports\2015_XYZArep009.xlsx[/TD]
[/TR]
</tbody>[/TABLE]

So where I am looking for (if possible) is I can create multiple rows based upon the scanning result within a cell containing a pattern (in this case "H:\common_place\reports\2015_" ), these (new) rows do have to take with them the adjacent column date ( in this case Region ).

Many thanks in advance.

 

[JLGWhiz]

Sub extractfiles()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, fAry As Variant, i As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 3).End(xlUp).Row
    For Each c In sh1.Range("C2:C" & lr)
        fAry = Split(Trim(c.Value), " ")
        For i = LBound(fAry) To UBound(fAry)
            If fAry(i) Like "H:\common_place\reports\2015_*" Then
                c.Offset(0, -2).Resize(1, 2).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
                sh2.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) = fAry(i)
            End If
        Next
    Next
End Sub