Solved: add additional data to word document's bookmark in excel

[Kenneth Hobs]

Now that other's see that you have additional needs, we can take this discussion off-forum. The solution for your 2nd example is more involved but has some similar elements. The comments below should help with your 2nd example.

What I try to do is to teach people how to do their projects and not do big projects for them. I prefer to teach concepts. Doing a whole project takes lots of time.

If you are still stuck after my comments below, send a completed MSWord file to me in email and I will work on it as I get time. I suspect that you have other requirements based on your first example. Most projects that place Excel data into an MSWord form file, at most just use a sheet of data to fill a one page MSWord form. The data is already in Excel rather than further processing being required as I did in the Function. If you color code an Excel file with the data placed into the MSWord file in the same color, that would help.

The first thing that I want to show you is how to get information about your Excel named ranges.

[vba]Sub NamesInfo()
Dim n As Name
On Error Resume Next
For Each n In ActiveWorkbook.Names
Debug.Print "Boomarkname: " & n.Name
Debug.Print "Value: " & n.Value
Debug.Print "Eval. Value: " & Evaluate(n.Value)
Debug.Print "Worksheet:" & Range(n.Name).Worksheet.Name
Debug.Print "Address:" & Range(n.Name).Address
Debug.Print vbCrLf
Next n
End Sub[/vba]
In VBE's Immediate window you will see that you have some named ranges with #Ref errors. Those will need to be resolved. For your project, it is probably best to use global Names and not local sheet named ranges.

There are two ways to set a range. (1) Activate the worksheet and use relative addressing. (2) Absolute addressing where the range can be found without activating that sheet. Absolute addressing is really just a more qualified range. They can be even more fully qualified or absolute so-to-speak if we also added the workbook reference. To illustrate these concepts for this example, I first added a Sheet to your first example where the range name of InmateAssault exists.

[vba]
Sub RelativeAddressing()
Dim n As Name, col As String, rn As Long

Set n = Range("InmateAssault").Name
Worksheets(Worksheets.Count).Activate 'Force activation of last worksheet.
Worksheets(Range(n.Name).Worksheet.Name).Activate 'Activate the sheet where the name exists.

col = "F"
rn = Range("B" & Rows.Count).End(xlUp).Row
MsgBox Range(col & "5", Range(col & rn)).Address, vbInformation, ActiveSheet.Name
End Sub

Sub AbsoluteAddressing()
Dim n As Name, col As String, ws As Worksheet, rn As Long

Set n = Range("InmateAssault").Name
Worksheets(Worksheets.Count).Activate 'Force activation of last worksheet.

Set ws = Range(n.Name).Worksheet
col = "F"
rn = ws.Range("B" & Rows.Count).End(xlUp).Row
MsgBox ws.Range(col & "5", ws.Range(col & rn)).Address & vbLf & _
"Notice that the active sheet name is: " & ActiveSheet.Name & vbCrLf & _
"and not the sheet name in the caption where the range exists " & vbLf & _
"for the named range, InmateAssault.", _
vbInformation, ws.Name
End Sub[/vba]
Now that you know how to use relative and absolute addressing, you can modify your code according to whichever method you like. I prefer to use absolute addressing normally. I used relative addressing for your first post as this usually meets most people's need.