I have a large data set in an Excel workbook currently. I have multiple tabs for various states (AL, MS, etc.), each tab contains the same data fields, but I needed to have separate tabs for how I originally setup my mail merge. I am looking to merge these all into a single tab, and use a "State" column to instead distinguish for mail merge purposes.

I have 3-5 Word documents for each state that pull various data fields depending on the document's specific merge fields. (doc1 pulls info from columns B,C,D,E,F. While Doc2 pulls info from columns A,C,E,F,G,H, and so on).

I currently use a Word macro that I found online that creates a separate word document and PDF file for each entry, which names and saves these files into a pre-existing file folder(s) that is pulled from a file path for each entry in the Excel file. (Column Z contains the Word document Name, ZA contains the Word Document file path to save into, ZB contains the PDF file name, ZC contains the PDF file path to save into.) I have some formulas set up in the workbook with a dropdown list so that I can quickly change the file and name path cells to alter the file naming convention and save location that is used based on what word document I am running the mail merge in.

This works great currently for running a larger batch of documents at one time, but when I have to run singular files, this is less ideal as I have to open the specific file before I can run the macro (each file is state specific, so I have found myself with 9+ different word documents open to run the various files). It is still pretty quick overall when compared to a manual mail merge, but it is easy to get overwhelmed and confused by the numerous Word documents that may be open at any given time.

So, my question then is, is there a way that I can have all of my data on a singular worksheet, that contains all of the data I need for any given state or document. Then have 3-5 macro buttons at the side of each entry that would open the respective word document, pull the specific mail merge data, and save the final PDF into a specific folder?

In essence I am trying to create a simple one stop shop to simply click a button and create these PDF documents and automatically place them into a specific folder. How that is accomplished I am open to suggestions. I understand there are probably limitations to what I am wanting to do, so feel free to crush my dreams haha.

More info may be necessary, so feel free to ask and I will post what I can. I have a decent amount of experience with formulas in Excel, some experience with Word mail merge and conditional if then else statements, and some Macro/VBA experience, though the last ones are minimal in comparison to the Excel formulas knowledge.

Also, this is my first time posting on this forum, so please excuse my lack of experience on here haha.

Thanks in advance for any help that is offered!