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

SirSchiz

New Member
Joined
May 4, 2011
Messages
24
Good Morning,

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

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:
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top