IMO the way jmac has suggested is probably the simplest and most reliable unless you have access to good coding resources. Or accept that you have to copy and modify your Mail Merge Main Document to merge data from each sheet.
There are other ways, but most involve programming or methods that I would say are not reliable enough for typical scenarios, and the other one will only work in some circumstances.
What can be used also depends on whether you want to combine all the data from several sheets into a single output, or whether you want separate outputs per sheet.
If you are using ADDRESSBLOCK or GREETINGLINE fields, which rely on field mapping, things are even trickier.
For "separate outputs per sheet",
a. if every sheet uses the same columns, but with different names, you may be able to use the column number instead of the name. For example, suppose your workbook contains 2 sheets, Sheet1 and Sheet2, and on Sheet1 column 1 is called namea and on Sheet2 column 1 is called nameb. Then (using Alt-F9 to look at the field names) you would need the following in your Mail Merge Main Document
for Sheet1:
{ MERGEFIELD namea }
for Sheet2
{ MERGEFIELD nameb }
But
{ MERGEFIELD 1 }
should work in both cases. (This isn't documented anywhere I know).
b. you could use "Address fields" rather than "Database fields". This suffers from a limitation which I would normally think of as a showstopper (I'll describe it later), but it works like this:
Instead of using the column name ("namea") via { MERGEFIELD namea }, you use an Address Field like this
{ MERGEFIELD "First Name" \m }
This uses a standard set of field names which you "match" (or "map") to the column names in your Excel sheet. You can pick these fields from the full Insert Field dialog box by selecting the "Address" option instead of the default "Database" option, and you can remap the fields using the Match fields... button in that dialog box. You need to check the Remember this matching for this set of data sources on this computer box at the bottom. Then, when you switch from one data source to another, Word should remember the correct mappings.
That's the theory, but in practice I think it is an unworkable approach in most scenarios. That's because Word only remembers (in the Windows Registry) one mapping for each Database name. So if you have a field called namea in one sheet and you need to map that to First Name, but in another sheet you need to map namea to Last Name, you can't do it (AFAICS). Worse, if someone changes the mappings, existing merge documents could end up pulling in the wrong data and the user would have no warning at all.
Approaches that involve programming include the following:
c. you have code (e.g. VBA code) which collects and saves the names you need in each sheet, then inserts the correct names for each MERGEFIELD field. You could use nested fields for that, in effect doing your own mapping. Using the example in (a) again, you could for example have a document variable called "thename", use VBA to populate it either with namea or nameb, and use the following nested field:
{ MERGEFIELD { DOCVARIABLE thename } }
(and this only works if you can get your VBA to make the substitutions before Word tries to verify the field names)
d. (if you require your chosen sheets to be merged in a single merge, not one merge per sheet), you either write code to automate Excel to combine the sheets using the Excel object model, or you get Excel (or Access) to do it by setting a up a UNION query to combine the sheets. Using our example again, the UNION might look like this:
SELECT 1 AS [thesheet], namea AS [thename] FROM [Sheet1$]
UNION
SELECT 2, nameb FROM [Sheet2$]
Although you can set up a query along these lines in Word VBA, Word has a limitation of 511 characters in its MailMerge queries that you are highly likely to exceed. You can set up a query in a new Excel Workbook to do something similar, but I do not know whether there are query length or other limitations in that product. Also, you will probably lose any "memo" data (longer than 255 characters), so again, this is only an option for some types of data source.
