Extract cells in column and open word and insert into bookmark.

[molesy01]

Hi
I have Sheet.(Section A) that has text in column "E", this text would start in row 10, and could have numerous rows, say 30. I am trying to extract the text in the cells in Column E, open a template in word and insert into a bookmark. I have mastered the code to extract a cell, open the word template, and insert into a bookmark. That is where my knowledge ends.

This question is twofold.
I would like to try and understand the code to extract more than one cell in column E, it would start in r,10 and could finish in r,30.
Also i have about 10 sheets which have the same attributes with regard to same column and same cells, but obviously different text.

I have started some code but all it does is open 2 documents.
[
Sub ExportButton()

Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Section A")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Activate
objWord.Documents.Open "C:\Users\Desktop\L2Template3.docx"
With objWord.ActiveDocument
.Bookmarks("DescriptionField").Range.Text = ws.Range("E10").Value

End With
With objWord.ActiveDocument
.Bookmarks("NameField").Range.Text = ws.Range("E12").Value
End With

Set objWord = Nothing

Set ws = ThisWorkbook.Sheets("Section B")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Activate
objWord.Documents.Open "C:\Users\Desktop\L2Template3.docx"
With objWord.ActiveDocument
.Bookmarks("StephenField").Range.Text = ws.Range("E10").Value

End With
With objWord.ActiveDocument
.Bookmarks("JohnField").Range.Text = ws.Range("E12").Value
End With

Set objWord = Nothing

End Sub
]
hope someone can help

 

[NdNoviceHlp]

For sheets SectionA and SectionB... what about SectionC to SectionI? What about additional row items? Dave

 

[molesy01]

Hi Dave

I've decided to change the way this works, I've created an example with one sheet called Draft Report and each cell is associated with a bookmark. Even though its restrictive its easy to code. Thanks

.Bookmarks("RelPartyDisc").Range.Text = ws.Range("E12").Value
.Bookmarks("Weather").Range.Text = ws.Range("E14").Value
.Bookmarks("StatusProp").Range.Text = ws.Range("E16").Value

 

[NdNoviceHlp]

You are welcome Steve. Glad you got it sorted out. Thanks for posting your outcome. Dave

 

[molesy01]

Hi Dave. Thanks for your help. dont know if you can help, when i create a new sheet with vba using a userform is there a way of filling the sheet with a chosen colour with code.

 

[NdNoviceHlp]

Not real sure what you mean but if you want to adjust the font/format of the worksheet, the link below has an overview and may help. If not, it would probably be better to start a new thread with a new topic. Dave

Excel VBA Font (Color, Size, Type, and Bold)

To make changes to the font in a cell you need to use the VBA‌ font object which includes properties, like, font color, font size, and font name.

excelchamps.com

 

[molesy01]

Not real sure what you mean but if you want to adjust the font/format of the worksheet, the link below has an overview and may help. If not, it would probably be better to start a new thread with a new topic. Dave

Excel VBA Font (Color, Size, Type, and Bold)

To make changes to the font in a cell you need to use the VBA‌ font object which includes properties, like, font color, font size, and font name.

excelchamps.com

Hi Dave. Thanks for your help. Steve