Selecting Cells Based on a value and converting text to columns

[Satyaprakash]

Hello People,
I need a small help. I am working with huge amounts of data and the first step is to sort it.
My original file has a lot of info. I am uploading a sample.
I have:
1. to select all the data after the cell that contains "Run Time : 23:32:43 RR Balance Sheet Account Composition - Contract Detail User :".... This is the last of all the headings.
2. Then, copy them to a new sheet
3. convert the text to columns.

Only then can I do my analysis.
Can anyone help me with this.

 

[Luke M]

How's this?

Sub FindMoveSplit()
Dim fCell As Range
Dim copyRange As Range
Dim destWS As Worksheet

'Turn screen off temporarily
Application.ScreenUpdating = False
'Find the cell of interest
Set fCell = Range("A:A").Find(what:="Balance Sheet Account COmposition - Contract Detail", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
'IF cell not found, do nothing
If fCell Is Nothing Then Exit Sub

'We'll copy from just below this cell to the last cell
Set copyRange = Range(fCell.Offset(1), Cells.SpecialCells(xlCellTypeLastCell))
'Add a new worksheet
Set destWS = Worksheets.Add(after:=ActiveSheet)
'Copy the data to new sheet
copyRange.Copy destWS.Range("A1")
'Text to columns with space as the delimiter
destWS.Range("A:A").TextToColumns Space:=True
'Turn screen back on
Application.ScreenUpdating = True

End Sub[code]

 

[Satyaprakash]

Wow! That code work liked a gem....
Thank you very much!