Dynamically loop through validation drop-down list after each entity's Word document is created

  • Hello,

    Thank you in advance for any advice that may be offered!

    Am working to assist a colleague with VBA;

    Currently, the code works great to create a Word document (report) with an entity's relevant information with respect to data taken from selected cells and tables set up in Excel based upon validation drop-down box (Sheet "Table", column "B2").

    The functionality that is lacking that we'd like to add is to have the drop-down box go to the next non-null value in a loop until all of the Word documents get created; for now, we manually advance to the next non-null value.

    I have commented out what I have tried in terms of setting up a loop thus far; the loop goes through the drop-down box but does not generate each entities Word document.

    Many thanks again,


    'The purpose of the routine is to create a new Word document (report) for each entity

    'The performance results and payment are pasted into the Word document and saved with a stamp of the entity name and generation date

    'Currently, the drop down box must be manually clicked from sheet "Table" column "B2" to go to the next entity

    Edited once, last by royUK: Add Code Tags ().

  • Welcome to Ozgrid. Please take a few minutes to read the Forum Rules to understand how the Forum works. I will add Code Tags this time.

    I cannot see why tou cannot do all this within Excel. Attach an example workbook.

  • Roy,

    Thank you - it's currently on letterhead...I can upload a Word doc with the bookmarks for the addresses, project and table placeholders if that helps.

    Option Explicit - not sure why it was removed - I'll read the link and glad to have it added back!



  • I've just tested the code but there;s missing bookmarks, probably because you have set up a dummy doc.

    I'm not quite sure what you mean about the drop down though.

  • That's ok - I put in the most important bookmarks for the name, addresses and table 1 (others can be ignored - I could comment out).

    The drop-down is the listing of all the hospitals (I only put 3 in the dummy list).

    The way the code works currently is that you manually choose one hospital from the drop-down, run the macro, and populate/save one report for that hospital.

    What would be good is if a mechanism could iterate through the columnar list of the hospitals to create each report in Word, save it, then move on to the next hospital until the max hospital is reached.

    Sorry if that was unclear - this last point is the sticking point that I'm trying to accomplish; please let me know any other questions!

    Thank you,


  • This code is probably better than what you have. It needs a corresponding Named Range in Excel to the Bookmark in Word. By that I mean the names must match. If the named range in Excel is empty it will be ignored.

  • Right - I understand that; I was trying to test with a named range "Hospital_Names" for the first one that I created.

    I wasn't clear in the code you sent where the range is explicitly declared; my trying isn't working.

    I'll do some reading on the named ranges in VBA to familiarize; thank you,


  • The code loops through each Named Range in the worksheet, checks for a match in the word doc and if it exists it copies the contents to the word doc. If the range is empty it ignores it

  • T think this is what you were trying for.

    Run the macro called Main. This will run through each choice in the Data Validation List and create the document for each one

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!