If row 2 has names in every column, it is not possible to put the hyperlinks in a column across from the name.
I have assumed that the names are in every row in column B and that the hyperlinks are in column C.
This macro works by first identifying the number of rows containing data in column B.
Then it tests the adjacent cells in Column C for a hyperlink.
If there is no hyperlink in the adjacent cell the macro creates a new Word document, then names it with the name in the cell in Column B and saves it. Finally it creates a hyperlink.
The path to these files is contained in the program and you will have to change it to match your folder structure/path.
In the worksheet containing the names create a command button and attach the macro to it as follows:
On the names Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Create Documents' or some other useful name.
Right-click the button again and select View Code
From the VB window menu bar select Tools - References ...
In the 'References - VBA Project' dialog box, find 'Microsoft Word 11.0 Object Library' and check the check box, click OK
(If you select Microsoft Word 11.0 this will work for Word 2003 and later. If you are using an older version of Word look for an earlier version number. Also 12.0 will only work with Word 2007).
In the code window on the right side enter this:
Private Sub CommandButton1_Click()
Dim rngFirst As Range
Dim rngLast As Range
Dim rngCell As Range
Dim appWord As Word.Application
Dim strPath As String
On Error GoTo ErrHnd
'create link to word
Set appWord = Word.Application
'set path for Word documents - include closing "\"
strPath = "C:\Temp\"
'set address of first name in column B
Set rngFirst = .Range("B2")
'find last record in column B
Set rngLast = .Range("B65534").End(xlUp)
'loop through all cells with data in column B
For Each rngCell In Range(rngFirst.Address, rngLast.Address)
'test if adjacent cell in column C has a hyperlink
If rngCell.Offset(0, 1).Hyperlinks.Count = 0 Then
'no hyperlink, so create a Word doc
'save new document with name from column B
Filename:=strPath & rngCell.Text & ".doc"
'add text to the document
.Documents(rngCell.Text & ".doc").Activate
'close document saving changes
.Documents(rngCell.Text & ".doc").Close SaveChanges:=wdSaveChanges
'create text in cell in Column C
rngCell.Offset(0, 1).Value = "Open " & rngCell.Text & "'s document"
rngCell.Offset(0, 1).Hyperlinks.Add Anchor:=rngCell.Offset(0, 1), _
Address:=strPath & rngCell.Text & ".doc"
'size column C to fit all new hyperlinks
Range(rngFirst.Offset(0, 1).Address, rngLast.Offset(0, 1).Address). _
Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.
Click Save from the Visual Basic Menu.
Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).
Make sure MS Word is open.
Click the 'Create Documents' button.
Note that because of the way that the code identifies the last record in column B, all cells in column B below the last record must be empty.
You could add code to ensure that MS Word was open, at present if Word is not open the Macro will fail.