You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's author is MVP Bill Jelen of MrExcel.com.
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. The workbook must contain two sheets, one named "Addresses" and one named "Labels". 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 are rearranged and copied onto the Labels sheet.
Sub CreateLabels() ' Clear out all records on Labels Dim LabelSheet As Worksheet Set LabelSheet = Worksheets("Labels") LabelSheet.Cells.ClearContents ' Set column width for labels LabelSheet.Cells(1, 1).ColumnWidth = 35 LabelSheet.Cells(1, 2).ColumnWidth = 36 LabelSheet.Cells(1, 3).ColumnWidth = 30 ' Loop through all records Dim AddressSheet As Worksheet Set AddressSheet = Worksheets("Addresses") FinalRow = AddressSheet.Cells(65536, 1).End(xlUp).Row If FinalRow > 1 Then NextRow = 1 NextCol = 1 For i = 2 To FinalRow ' Set up row heights If NextCol = 1 Then LabelSheet.Cells(NextRow, 1).Resize(4, 1).RowHeight = 15.25 LabelSheet.Cells(NextRow + 4, 1).RowHeight = 13.25 End If ' Put the Name in row 1 ThisRow = NextRow LabelSheet.Cells(ThisRow, NextCol).Value = AddressSheet.Cells(i, 1) & " " & AddressSheet.Cells(i, 7) ' Put the Address Line 1 in row 2 If AddressSheet.Cells(i, 2).Value > "" Then ThisRow = ThisRow + 1 LabelSheet.Cells(ThisRow, NextCol).Value = AddressSheet.Cells(i, 2) End If ' Put the Address Line 2 in row 3 If AddressSheet.Cells(i, 3).Value > "" Then ThisRow = ThisRow + 1 LabelSheet.Cells(ThisRow, NextCol).Value = AddressSheet.Cells(i, 3) End If ' Put the City, State, Country/Region and Postal code in row 4 If AddressSheet.Cells(i, 4).Value > "" Then CitySt = AddressSheet.Cells(i, 4) End If ThisRow = ThisRow + 1 LabelSheet.Cells(ThisRow, NextCol).Value = CitySt ' Update the row and column for the next label If NextCol = 1 Then NextCol = 2 ElseIf NextCol = 2 Then NextCol = 3 Else NextCol = 1 NextRow = NextRow + 5 End If Next i LabelSheet.Activate Else MsgBox "No records match the criteria" End IfEnd Sub
Comments: (10) Collapse
Excellent example. Just started to follow this RSS and find it interesting and informative. Thank you.
The problem with this solution is that in a business setting, it is messy to modify and maintain, as few people use VBA, and those that do know VBA don't like slogging through other people's code.
What I would do, is set up a label sheet,pre-formatted perfectly, with a counter cell, so that if I enter(say) 6, the data for the 6th address loads itself into the sheet (using OFFSET formulae). Then it only takes a small macro to automate all the data through the sheet, one record at a time, by changing the counter cell and then printing or copying the sheet.
The result is that the layout and formatting can be easily modified by any moderately competent user, rather than being inaccessible in a VBA black box. The key strengths of Excel are transparency and flexibility, and it's important we don't dilute them if we don't need to.
What I am not getting here is, what is the functionality of this piece of code that can not be achieved using mail merge in MS word, using the said addresses sheet as the backend database.
No programming needed, easy to implement and understand. This is of course based on the limited understanding that I have about the exact context and scenario. Please enlighten me.
Hi Viabhav,
Yes you can use mail merge - there is a help article about how to use it here: office.microsoft.com/.../HP102432671033.aspx
This scenario is for those who want to have their labels formatted and saved in Excel, rather than in Word.
Thanks,
Allison
I am trying to print labels on dark blue colored labels and I want the type to be White with Excel 2007. I select white as the type color but it will not print pure white lettering. It will print a faint gray lettering. The font color on the screen shows white but it does print white on the blue labels.
I am printing on a lexmark ink jet printer.
Am I missing something in the settings of color type for the lettering?
Hi Joe,
Your question has to do with the printer capabilities. Typically white is printed as the absence of ink (on white paper). See this article for more information about printing white ink: desktoppub.about.com/.../white_ink.htm
You might contact Lexmark for additional information.
Hello,
I am new to this RSS Feed and am finding it very helpful. I am still fairly new to Excel but am a very quick learner and have tought myself all that I know about Excel. I would love to use this code example but am wondering where I enter the code exapmle for this to work? I am sorry if this is a stupid question.
Thanks alot!
Hi Amber,
Glad you are finding the blog postings useful. Here are some links to intro material about the Visual Basic Editor and macros:
office.microsoft.com/.../HP100141111033.aspx
msdn.microsoft.com/.../ee814737(office.14).aspx
Let me know if you need more information!
I'm not sure why that link to MSDN won't paste correctly - the title of the article is "Getting Started with VBA in Excel 2010"
I have do excel before cant remember the formula to get it started can you help me with that.
Thanks
Julia
Comments: (loading) Collapse