VBA Expert Needed:Copy, Paste, Formatting Challenge : Excel 2007

[SirSchiz]

Good Morning,

I am trying to be consise here,please bare with me.

I have a unique issue. I have a macro that will search for a particular term, say "Hi There". Once it finds this term it will select everything until the last used cell, copy to a variable, then it creates a new workbook and new worksheets, then pastes the variable's contents to a new worksheet based on index, then rename each new worksheet with the 1st worksheets index name. It then loops through all worksheets and repeats the steps until all worksheets are accounted for.

Now, I'm stuck on formating the new sheet. I also need it to go through and on each sheet of the new work book, find 1-3 paragraphs, take these paragraphs and place each one into a new cell say D:4, D:6, D:8. The hard part is that each paragraph contains different statements and varies in length. The only constant is that each paragraph is separated by a blank row.

I have a sample Data Sheet, but do not see how to add an attachment.

Here's my code so far: SAMPLE DATA PASTED BELOW CODE:

 Sub SearchSelect()

    Dim NeWBk, NeWBk1 As Workbook
    Dim Wsht As Worksheet
    Dim rngFoundCell As Range
    Dim strSearchTxt As String

    Set OrigWBk = ActiveWorkbook
    Set NeWBk1 = Workbooks.Add
    
    'Using Input Box to set search Term.
    strSearchTxt = InputBox("Please Enter Your Serch Term: ")
    
 'Creating new workbook, adding new worksheets based on how many there are in 1st Wokrbook.
Do Until NeWBk1.Sheets.Count = OrigWBk.Sheets.Count
         NeWBk1.Sheets.Add After:=NeWBk1.Sheets(NeWBk1.Sheets.Count)
         
         
Loop



For Each Wsht In OrigWBk.Worksheets

'Setting my range variable.

Set rngFoundCell = Wsht.Cells.Find(What:=strSearchTxt, After:=[A1], _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
        'Selecting and pasting to each new worksheet of new workboo.
        Wsht.Range(rngFoundCell, rngFoundCell.End(xlDown)).Copy
        NeWBk1.Sheets(Wsht.Index).Range("A1").PasteSpecial xlPasteValues
        NeWBk1.Sheets(Wsht.Index).Range("A1").PasteSpecial xlPasteColumnWidths
        'Renaming based on Index name of 1st Workbook.
        NeWBk1.Sheets(Wsht.Index).Name = Wsht.Name
        
Next Wsht

'Clean up - Resetting my variables for next process.

Set NeWBk1 = Nothing
Set OrigWBk = Nothing

End Sub/[CODE]
==================
SAMPLE DATA: (Imagine there are 500 worksheets that has this type of data)

Hi There

pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp

pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp

ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp

ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp

pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
ppppppppppppppppppp

ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
ppppppppppppppppp

pppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppp

ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp