1. Make a separate sheet for your data table 2. If your data is in another application like Access, use Data/Get External data to query the columns and rows that you want. If your data is already in Excel, if you have formatted your table properly (one table per sheet, headings in row 1 starting in column A, table is contiguous) then you can use the technique in step 2 as well.
B. Setting up the "form"
The objective is to MAP data from selected columns in your Source Data Table to the Form, using Named Ranges. If you do not know what a Named Range is, please refer to Excel HELP.
1. Format the page(s) the way that you want your form to look 2. Each Merge Cell should have no other data in it. 3. Each Merge Cell should be named with the same name as the column heading on the data sheet. NOTE: if any data column heading contains a space, then substitute an UNDERSCORE character for each space. For instance, a data heading like First Name should have a corresponding Merge Field name of First_Name.
C. Running Mail Merge
1. Select the rows that you want from your data using the AutoFilter or Advanced Filter if you filter in place. 2. Run MergePrint from Tools/Macro/Macros or a button.
D. The code that runs the Mail Merge
There is a procedure (macro) called MergePrint and a function called RangeName. Paste both of these into a MODULE in the Visual BASIC Editor. If you wish, you insert a Button on your sheet Form to run MergePrint.
Function RangeName(sName As String) As String RangeName = Application.Substitute(sName, " ", "_") End Function Sub MergePrint() 'set up your merge form by naming the merge fields _ with the same name as the data fields you are importing. 'if any data field contains spaces, then substitute an _ UNDERSCORE character for each space in the name. Dim wsForm As Worksheet, wsData As Worksheet Dim sRngName As String, r As Long, c As Integer Set wsForm = Worksheets("My Form") 'change to your sheet name Set wsData = Worksheets("My Data") 'change to your sheet name With wsData.Cells(1, 1).CurrentRegion For r = 2 To .Rows.Count If Not wsData.Cells(r, 1).EntireRow.Hidden Then For c = 1 To .Columns.Count sRngName = wsData.Cells(1, c).Value Range(RangeName(sRngName)).Value = wsData.Cells(r, c) Next wsForm.PrintOut End If Next End With End Sub