Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. He is a regular guest on TechTV with Leo Laporte and is the host of, which includes more than 300,000 questions and answers about Excel. MVP Bill Jelen is the author of more than two dozen books about Microsoft Excel.
' Update the row and column for the next label You can do the same from the Print dialog under File and then by going to PAge Setup and Sheet. LabelSheet.Cells(ThisRow, NextCol).Value = CitySt If AddressSheet.Cells(i, 4).Value > "" Then ' Put the City, State, Country/Region and Postal code in row 4 LabelSheet.Cells(ThisRow, NextCol).Value = AddressSheet.Cells(i, 3) To do this, you’ll need to create columns for each bit of address information. Step 1 Making Your Microsoft Excel Worksheet To begin with, you’ll need to make your Excel Worksheet filled with the address details. Under that, select Label vendors as Avery US Letter, Product number as 5160 Address Labels. A new pane called Label Options will open up.
If AddressSheet.Cells(i, 3).Value > "" Then Below are the steps on how to print address labels from excel. Step 3 Set up Labels in a Blank Word Document In a blank word document, Go to > Mailings, select > Start Mail Merge, select > Labels. LabelSheet.Cells(ThisRow, NextCol).Value = AddressSheet.Cells(i, 2) If AddressSheet.Cells(i, 2).Value > "" Then
LabelSheet.Cells(ThisRow, NextCol).Value = AddressSheet.Cells(i, 1) & " " & AddressSheet.Cells(i, 7) LabelSheet.Cells(NextRow + 4, 1).RowHeight = 13.25 Select Create New and then Labels.Select which printer you have, and choose the labels you are going to print on using the drop down menu. LabelSheet.Cells(NextRow, 1).Resize(4, 1).RowHeight = 15.25
Set AddressSheet = Worksheets("Addresses")įinalRow = AddressSheet.Cells(65536, 1).End(xlUp).Row Sub CreateLabels () ' Clear out all records on Labels Dim. The addresses are rearranged and copied onto the Labels sheet. The addresses are rearranged and copied onto the Labels sheet. The addresses on the Addresses sheet must be arranged as one address per row, with the Name in Column A, Address Line 1 in Column B, Address Line 2 in Column C, and the City, State, Country/Region and Postal code in Column D. The addresses on the Addresses sheet must be arranged as one address per row, with the Name in Column A, Address Line 1 in Column B, Address Line 2 in Column C, and the City, State, Country/Region and Postal code in Column D. The workbook must contain two sheets, one named "Addresses" and one named "Labels". The following code example takes a list of addresses arranged as one address per row, and copies them onto another sheet, arranging them to fit on printable address labels.