Add Text to String, Output to Wordpad/Word

[Jimbo42]

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!