Search column to find string then copy string parts to new worksheet in different columns

I have a log that is written every time a transaction happens in the system.
I have just moved into a new role and am being asked to analyse these logs often for issues with counts.
each of the logs has a two columns

The log is captured in a worksheet which takes on the day as its name that is
Day07 for the seventh day of the month
Day08 for the eighth and so on

The first column is always a data column
The second column is always a string of data

There are only two columns in the source data.

The following is an example of the source data

7/07/2015 14:42:47 : B19 1 Rod(s) drawn/pushed successfully on system

I would like to be able to search column B for the following

B19

I would then like to perform the follow per message

When B19 is detected I would like to copy that cell to a new worksheet called Data in the following format
|7/07/2015| |14:42:47| |:| |B19| |1| |Rod(s) drawn/pushed successfully on system|
Date should go to column A for all messages
Time should go to column B for all messages
The colon should go to column C for all messages
The message ID should go to column D for all messages
The digit (1) should got to column E
All other data should go to Column F

Each message should be saved to a new row in the data sheet.

I had intended on doing a loop in vba to iterate through the rows but am having trouble with the search syntax and copy / paste functions.
IT SSMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Sub Macro()

For Each sh In ActiveWorkbook.Worksheets
    If sh.Name Like "Day*" Then
        For Each c In sh.Range(sh.Range("B1"), sh.Range("B" & Cells.Rows.Count).End(xlUp))
            If c.Value Like "*B19*" Then
                arrC = Split(c, " ")
                If Evaluate("=ISERROR('Data'!A1)") Then
                    Set newSh = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
                    newSh.Name = "Data"
                    newSh.Range("A:A").NumberFormat = "dd.mm.yy"
                    Rw = 1
                Else
                    Set newSh = ActiveWorkbook.Sheets("Data")
                    Rw = newSh.Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
                End If
                newSh.Range("A" & Rw) = c.Offset(0, -1)
                newSh.Range("B" & Rw) = arrC(0)
                newSh.Range("C" & Rw) = arrC(1)
                newSh.Range("D" & Rw) = arrC(2)
                newSh.Range("E" & Rw) = arrC(3)
                newSh.Range("F" & Rw) = Mid(c, Len(arrC(0) & " " & arrC(1) & " " & arrC(2) & " " & arrC(3)) + 2)
            End If
                
        Next
    End If
Next
End Sub

Open in new window

Regards
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You have shown only one sample or source data which is this....
7/07/2015 14:42:47 : B19 1 Rod(s) drawn/pushed successfully on system

Show some more sample data to know how the data changes in the source file.
Or even it would be better if you upload a sample file with some sample data to adopt the correct approach to achieve the task.
IT SSMAuthor Commented:
Hi please find attached sample file to show the scope of the data. For the rows I am looking for the only change will be the number of rod(s)
sample.xlsx
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

IT SSMAuthor Commented:
Rgonzo1971 -- I have tried your code it seems to be processing the first couple of lines then rolling over the loop without processing the code. No Errors generated. thanks please see sample data
I should have added I am using excel 2013
Rgonzo1971Commented:
then try it seems that example if for your other question

Sub Macro1()
ActiveWorkbook.Sheets("Source").Activate
For Each c In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    If c.Value Like "*B19*" Then
        arrC = Split(c, " ")
        If Evaluate("=ISERROR('Data'!A1)") Then
            Set newSh = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
            newSh.Name = "Data"
            Rw = 1

        Else
            Set newSh = ActiveWorkbook.Sheets("Data")
            Rw = newSh.Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
        End If
        newSh.Range("A" & Rw) = arrC(0)
        newSh.Range("B" & Rw) = arrC(1)
        newSh.Range("C" & Rw) = arrC(2)
        newSh.Range("D" & Rw) = arrC(3)
        newSh.Range("E" & Rw) = Mid(c, Len(arrC(0) & " " & arrC(1) & " " & arrC(2) & " " & arrC(3)) + 2)
    End If
  
Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT SSMAuthor Commented:
This works perfectly thanks Rgonzo1971
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.