print on used label sheet

[bobwiebe]

I want to use up label sheets where only a few labels have been used. This is in the context of a merge mailing. I have looked on line, and studied the label set up screens, to no avail as yet. Whereas some of the online advice shows pictures of a selection window where one can ask the printing to start in a certain column and row on the lable sheet, this option does not show up in the screens I see. I have also tried leaving empty rows in the source document for the labels, but this also does not have the desired effect. I am probably missing something as plain as the nose on my face, but cannot see it. Any tips?

[macropod]

Cross-posted at: http://answers.microsoft.com/en-us/o...4-1d6c64d3d81c
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

Other than inserting the required # of blank records in your data source so they get processed first, there is no simple solution. Anything more sophisticated would end up using macros rather than a mailmerge.

[bobwiebe]

Thanks, Paul. How do I insert the # of blanks in the data source. When I tried this earlier it did not seem to affect the placement of the labels. I have tried both with actual blank lines and lines where I keyed in the word blank for one column.

[macropod]

Assuming it's an Excel workbook, you simply insert however many empty rows you need before the first data row. IMHO, though, unless you're only merging a few rows at a time, resulting in a large proportion of unused rows, it's not worth the effort. If you're working with the same few rows each time, you may even do better to merge multiple copies of the same labels in one go, or to forego mailmerge and link the cells to Excel, then move the links in the Word document to whatever cells you want to print on.

[bobwiebe]

Thanks, Paul. I am not familiar with the part after forgo mail merge, e.g. linking cells. I did have some luck putting in blank rows. Instead of making them truly blank, i copied one row of data the number of times I needed blanks.

[macropod]

FWIW, an alternative approach would be to use a Directory merge. Setting this up for the first use is a bit fiddly but, from then on there's no need to modify the data source. It also changes the way the output is generated, from an across-then-down arrangement to a down-then-across arrangement.

What you do is:
1. In the Labels dialog, click New Document to create a new document based on the label definition you have chosen.
2. Display table gridlines (Table Tools | Layout | Table | View Gridlines) and nonprinting characters (Ctrl+* or Home | Paragraph | Show/Hide ¶) so you can see what you're doing.
3. Delete all cells except the first. If there is a spacer column, note its width beforehand.
4. On the Page Layout tab, in the Page Setup group, click Columns and choose More Columns...
5. In the Columns dialog, select however many labels across your label stationery had
6. In the Spacing box, set the width to the original spacer column width or, if it had none, 0
7. If necessary, set the column width to your label width.
8. Change the mailmerge type to 'Directory'.
From then on, when you run the mailmerge and send the output to a new document. Once the merge is done, simply insert as many empty cells as you need (e.g. via the insertion of column breaks and/or by however many cells you need for the blanks then choosing Table Tools | Layout | Insert Above) before the first populated cell.

By adding the following macro to your Directory mailmerge main document, clicking on the 'Edit Individual Documents' button will intercept the merge, allowing you to specify how many empty/used labels there are, before sending the output to a new document. It will work with any data source and does so by modifying the mailmerge output and inserting however many empty rows of labels are required. With this approach, you should never lose an unused label.

Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim i As Long, j As Long
j = CLng(InputBox("How many labels have been used on the first sheet?", "Skip used labels"))
ActiveDocument.MailMerge.Execute
With ActiveDocument
  .Rows.AllowBreakAcrossPages = False
  For i = 1 To j
    .Rows.Add .Rows(1)
  Next
  End With
  '.PrintOut
  '.Close False
End With
Application.ScreenUpdating = True
End Sub

Note: The above code sends the merge output to a new document, not direct to printer, but it can be printed from there. To automate that and have the merge perform like a merge to print, un-comment the lines:

   '.PrintOut
   '.Close False

You should also note that this won’t be especially useful for using left-over labels from a conventional label merge. However it does obviate the need to modify the data source every time.

[bobwiebe]

Thank you.

[gmayor]

An alternative suggestion is to use http://www.gmayor.com/Envelope_Label_Add_In.htm which will print individual envelopes or labels from Excel, Outlook or typed at the keyboard, or batches of envelopes or labels from an Excel worksheet, without using mail merge. You can print as many of each label as required and start from any label.

Beware if using part used label sheets in a laser printer as some of them can get hot enough to detach labels and stick them to the drum, ruining the mechanism.