VBA Workbook - Sheets-Multiple Macros- Generate a single word.dox template

Hi All, im pretty new to all of this and hoping someone can help me...

I have a workbook with 7 sheets which i have renamed at the bottom of the workbook, each sheet will have a separate macro that will populate a single word.dox, on the last sheet i have created a button which i would like to run all macros to generate the single word.dox template

I currently have 3 issues:

Macro1: Works completely fine when i run the macro from the first tab: Code below

Sub ReplaceText()

Set ws = ThisWorkbook.Sheets("Pre Req")
Dim WordApp As Object
Dim wdoc As Object
Set wAPP = CreateObject("Word.Application")
wAPP.DisplayAlerts = False
wAPP.Visible = True

Set wdoc = wAPP.Documents.Add(Template:="C:\Users\ANash\Desktop\Site Survey VBA TEST 001.docx", NewTemplate:=False, DocumentType:=0)

With wdoc
.Application.Selection.Find.Text = "<<Customer>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("C2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "<<Customer Site>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("C3")
.Application.Selection.EndOf

End With

End Sub

Macro 2 - I need this macro to run directly after the 1st macro and extract information from the second sheet into a section in the word document; problem i have is that when i run macro 2 nothing happens in the word document .
Macro 2:

Sub ReplaceText()


With wdoc
.Application.Selection.Find.Text = "<<RO12>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("B12")
.Application.Selection.EndOf



End With

End Sub

Third issue i have is when i use the button to run the macros the word template opens.. but absolutely no information is extracted from Excel into the word.dox template ( but when i run the 1st macro in its sheet it will open the word.dox and pull the required information into the document.

Macro 3:

Sub Button1_Click()

Call Module1.ReplaceText 'Macro1
Call Module2.ReplaceText 'Macro2

End Sub


Any help or guidance will be greatly appreciated

when i run macro 2 nothing happens in the word document

Macro 2 doesn't seem to have any reference to the word document since it's declaration is within macro 1 only.
i.e. wdoc in macro 2 is nothing

Please Login or Register  to view this content.

I suspect because Range("C2") may be referring to the sheet with the button if that's the active one.

If you could attach a workbook, that would likely be helpful.
Are you always wanting to populate from all the sheets?

Hi ByteMarks;

Thanks for your assistance thus far - that must be what i am missing; as i not sure what the code is to reference the newly open word.docx - i originally had the same reference in Macro2 that Macro1 had.. as you can probably guess when i ran the Macro it continuously opened up multiple word.dox templates so i removed it as i could not figure out how to reference the open word.dox

Range ("C2") is referring to the actual information in Cell C2 contained within the sheet that information then replaces <<Customer>> in the word.docx template if that makes sense.

The document will be utilised as a site survey document which will populate a customer facing document so it will need to populate macros from all sheets.. If you really need a copy i will take out some sensitive information and send it... just let me know again thank you for you help and guidance

Hi Mark, i am still struggling to get this to work any assistance you might be able to give would be appreciated.

Hi ByteMarks,

I have now managed to generate the first macro when pressing the button from a separate sheet and it populates the required information in the document which is excellent... i am now trying to figure out the code to reference the open document for the second Macro any advice would be appreciated

That's great.
You try declaring WordApp and wdoc as public objects, outside of any specific Sub. That way it should be available to all of them.
Also, it might be an idea to qualify your ranges so that you explicitly state which worksheet to look at, e.g.

Please Login or Register  to view this content.

If you are always pulling data from all of the worksheets, it might be simpler to have one macro which loops through all of them?