split the log data into different sheets

[subhashuttur]

Dear all,

Basically i have sap log data in column "A". I want to split or extract specific log data to different sheet. If i want to extract "ISC Error-log for Suspend-file" it should pick row data from the below uptil next log defined. I searched different strings but didnt succeed.
Could someone see any best alternatives to reduce this manual task.

below the different logs:

ISC Error-log for Suspend-file
MIRO Error-log for Suspend-File
Workflow Error-log for Suspend-file
Softway Error-log for Supend-file


Thanks
subhas

 

[Marc L]

Hi,

you forgot to attach a desired result workbook …

​

 

[subhashuttur]

Hi Marc,

Attached the result file. 1st tab contains the input data. the subsequent tabs contains the result of different logs data. output log data can contain rows starts with "Transaction*".

Thanks,
subhas

 

[Marc L]

Subsequent tabs are already created in initial workbook ?

Are they always the same static four or for any "Error-log" ?

​

 

[subhashuttur]

subsequent tabs are created already in the workbook.

Currently we have these many static logs. I gave a few as example on my attached file.

ISC Error-log for R10IBDX-file
MIRO Error-log for R10IBDX-file
Workflow Error-log for R10IBDX-fil
ISC Error-log for Suspend-file
MIRO Error-log for Suspend-File
Workflow Error-log for Suspend-fil
Softway Error-log for Supend-file
Softway Error-log for PO not found

best regards,
subhas

 

[Marc L]

So it's like your second attachment but with no data in subsequent tabs ?
In this case, the code will check only transactions of each title sub tab …

Other way is from a workbook like your first attachment (w/o any sub tab)
to extract any "Error-log" transactions to new tabs …
​

 

[subhashuttur]

its like second attachment. with no data in subsequent tabs. code can check only title tabs & get the result.

Thanks
subhas

 

[Marc L]

Code should be within a master workbook ?

Is it always the same Book2 file to update ?

​

 

[subhashuttur]

either can be OK. Master workbook can also help

best regards,
subhas

 

[Marc L]

Must precise :

Source workbook path & name is always the same ?

What's your choice : code in source workbook or in another one ?
​

 

[subhashuttur]

source workbook please.

thanks
subhas

 

[Marc L]

Try this demonstration :​

Sub Demo()
           Dim Rg As Range
           Application.ScreenUpdating = False
For W% = 2 To Worksheets.Count
    With Worksheets(W)
        .UsedRange.Clear
               R% = 0
           Set Rg = Worksheets(1).UsedRange.Find(.Name)
        If Not Rg Is Nothing Then While Rg(3 + R).Value Like "Transaction *": R = R + 1: Wend
        If R Then Rg(3).Resize(R).Copy .Cells(1) Else .Cells(1).Value = "No data or bad tab name …"
    End With
Next
           Set Rg = Nothing
           Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[subhashuttur]

superb Marc. delighted

Thanks you made my work easy. Thanks a lot

 

[Marc L]

Optimized version if always "Report" after "Transaction" :​

Sub Demo1()
           Dim Rg As Range, Rs As Range
           Set Rs = Worksheets(1).UsedRange
           Application.ScreenUpdating = False
For W% = 2 To Worksheets.Count
    With Worksheets(W)
        .UsedRange.Clear
        Set Rg = Rs.Find(.Name)
         If Rg Is Nothing Then .Cells(1).Value = "No data or bad tab name …" _
                          Else Range(Rg(3), Rs.Find("  Report ", Rg)(0)).Copy .Cells(1)
    End With
Next
           Set Rg = Nothing:   Set Rs = Nothing
           Application.ScreenUpdating = True
End Sub

You may Like it !
​

 

[subhashuttur]

Thanks again