layu.blogg.se

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 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) If AddressSheet.Cells(i, 3).Value > "" Then 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 LabelSheet.Cells(NextRow, 1).Resize(4, 1).RowHeight = 15.25

Set AddressSheet = Worksheets("Addresses")įinalRow = AddressSheet.Cells(65536, 1).End(xlUp).Row 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 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.