excel value to word bookmark - macro to keep formatting

[excelor100]

Hi all,


I lost access to my old email account so having to post for the first time again. Have always found everyone here amazingly helpful and i manage to struggle by with macros compared to most here.


I have macro which I'm using to copy a cell range 'name' in excel to a bookmark in word with the same 'name'. It works perfectly (and i can't contest to writing it all). However I'm looking for a way to keep the formatting, can anyone help?


The issue I think is that it's copying over the cell value, but this means it loses any formatting when it reaches the word bookmark.


Using the cell 'name' ranges from excel is extremely helpful, as it means I can edit the excel document, add rows etc and the range name stays the same meaning I do not need to alter the macro. Whereas If i use a copy and paste macro with a keep source formatting code (which would work i think) it would need a lot of manual editing.


So I'm hoping someone may have a solution to keep the formatting with the below macro!


Any ideas hugely appreciated.

Sub DataMergeToWord()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim TodayDate As String
Dim Path As String


  Set wb = ActiveWorkbook
  TodayDate = Format(Date, "mmmm d, yyyy")
  Path = wb.Path & "\Test1.docm"
  
  On Error GoTo ErrorHandler


'Create a new Word Session
  Set pappWord = CreateObject("Word.Application")
  
  On Error GoTo ErrorHandler


'Open document in word
  Set docWord = pappWord.Documents.Add(Path)


'Loop through names in the activeworkbook
  For Each xlName In wb.Names
    'if xlName's name is existing in document then put the value in place of the bookmark
    If docWord.Bookmarks.Exists(xlName.Name) Then
      docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
    End If
  Next xlName


'Activate word and display document
  With pappWord
      .Visible = True
      .ActiveWindow.WindowState = 0
      .Activate
  End With


'Release the Word object to save memory and exit macro
ErrorExit:
   Set pappWord = Nothing
   Exit Sub


'Error Handling routine
ErrorHandler:
   If Err Then
      MsgBox "Error No: " & Err.Number & "; There is a problem"
      If Not pappWord Is Nothing Then
        pappWord.Quit False
      End If
      Resume ErrorExit
   End If


End Sub

Thanks!


EDIT:
To make it clear, by formatting I mean decimal points etc or format of cell in % etc.

I know i could have for example a £ sign in word, but it wouldn't have the comma in a monetary figure for example, and the data coming across has a lot of numerical figures.