Add Text to String, Output to Wordpad/Word

I could use some advice about a better way of running this Spreadsheet tool.

This tool is for tracking Branches that have recovered from a particular service outage. It distinguishes between those that have "Newly Recovered" and have not been reported yet to management, and those that have "Already been reported" to management but need to stay on the radar for a final tally at the end of the day.

The tool starts with the user clicking a drop down on the left to select a status of "NR" showing that it's a Newly Reported branch that has recovered. Hidden column "C" has a vlookup that copies the name of the branch into itself if the Row has a status of "NR".

Cell B94 calls on a function that looks for all branch names that are showing in hidden column "C" and concatenates them with a comma and a space in between each.

The "Step 2" button runs a macro that opens a Word Doc and pastes in the concatenated text from B94.

The "Step 3" button runs a macro that updates all rows with "NR" status to a status of "R" for end of day tracking purposes.

I would like to find a way to code something that:

1. Looks for all rows with a status of "NR" in column A
2. If column A has a value of "NR", then add the cell value in column B to a string variable which collects the entire list of branches in "NR status.
3. Copy the text "The following branches have recovered: " + the string contents to Word or Wordpad with a comma and space separating each branch name.

End result desired in Word or Wordpad would look like this: The following branches have recovered: Branch 1, Branch 2, Branch 3...

Thanks in advance for any ideas or suggestions!

Hello Jimbo42,

I have added the macro below to the "Print to Word" button in the attached workbook.

' Thread:  http://www.excelforum.com/excel-programming/801746-add-text-to-string-output-to-wordpad-word.html
' Poster:  Jimbo42
' Written: November 16, 2011
' Author:  Leith Ross

Sub CopyToWordFile()

    Dim Cell As Range
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Text As String
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim wdRng As Object
    Dim Wks As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A2")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
            
            For Each Cell In Rng
                If VBA.StrComp(Cell, "NR", vbTextCompare) = 0 Then
                   Text = Text & Cell.Offset(0, 1) & ", "
                End If
            Next Cell
            
        If Text <> "" Then
        
           Text = "The following branches have recovered: " & Left(Text, Len(Text) - 2)
           
           Set wdApp = CreateObject("Word.Application")
           Set wdDoc = wdApp.Documents.Add
           Set wdRng = wdDoc.Content
           
           wdApp.Visible = True
           wdRng.InsertAfter Text
           
           On Error Resume Next
           wdDoc.Close -2
           
        End If
        
End Sub

Hello Jimbo42,

You're welcome. Glad the code was clear and easy to understand. If you do have any questions, feel free to ask.

Alternative:

Sub snb()
 With CreateObject("word.document")
   .content = "The following branches have been recovered: " & vbCr & Join(Filter([transpose(if(outageWorksheet!A2:A400="","~",outageworksheet!B2:B400))], "~", False), vbCr)
   .SaveAs "E:\OF\recovered.doc"
   .Close 0
 End With
End Sub