Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate numbers for labels
Hi Excel experts.
I want to be able to generate some Labels for printing. I have an Avery add-on to Word that will Import a list of numbers from an Excel sheet, and then arrange such that they then print out 48 labels per A4 sheet. I'd like to be able to generate automatically the column of numbers to achieve this, by Inputting the number of sheets (so if you pick 2, 96 numbers are generated etc) and another number which is the Prefix (a branch code) So the list would look like List K81011_001 K81011_002 etc. etc. As an alternative, the second number could be the actual total of lines to generate instead of multiples of 48 Thanks for any help here. (Complete Macro/VBA novice) Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate numbers for labels
Below is the simple macro which does the task
It uses a FOR-NEXT loop to generate the sequential codes. You can also set the "number of sheets" there '--------------------------------------------------- Sub example( Dim tmp As Intege With ActiveSheet.Columns(1 For tmp = 1 To 48 * 2 'change the number of sheet her 'may set the prefix in this lin .Cells(tmp).Value = "K81011" & "_ " & Format(tmp, "000" Nex End Wit End Su '--------------------------------------------------- The above macro can be extended to use InputBoxes to collect user input variables (It also contains a line to ensure the prefix is Capital. '--------------------------------------------------- Sub example2( Dim tmp As Intege Dim respons Dim response D response = InputBox("Please enter the number of sheets." Loop Until IsNumeric(response response2 = InputBox("Please enter the prefix." With ActiveSheet.Columns(1 For tmp = 1 To 48 * respons .Cells(tmp).Value = UCase(response2) & "_ " & Format(tmp, "000" Nex End Wit End Su '--------------------------------------------------- Regards Edwin Ta |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate numbers for labels
Edwin,
That's just what I wanted.... well almost! I'd like the first cell that's written to be the word 'List' (The Avery Wizard takes the first line as the <<Merge field. Regards Neil "Edwin Tam (MS MVP)" wrote in message ... Below is the simple macro which does the task. It uses a FOR-NEXT loop to generate the sequential codes. You can also set the "number of sheets" there. '---------------------------------------------------- Sub example() Dim tmp As Integer With ActiveSheet.Columns(1) For tmp = 1 To 48 * 2 'change the number of sheet here 'may set the prefix in this line .Cells(tmp).Value = "K81011" & "_ " & Format(tmp, "000") Next End With End Sub '---------------------------------------------------- The above macro can be extended to use InputBoxes to collect user input variables. (It also contains a line to ensure the prefix is Capital.) '---------------------------------------------------- Sub example2() Dim tmp As Integer Dim response Dim response2 Do response = InputBox("Please enter the number of sheets.") Loop Until IsNumeric(response) response2 = InputBox("Please enter the prefix.") With ActiveSheet.Columns(1) For tmp = 1 To 48 * response .Cells(tmp).Value = UCase(response2) & "_ " & Format(tmp, "000") Next End With End Sub '---------------------------------------------------- Regards, Edwin Tam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generate random numbers | Excel Discussion (Misc queries) | |||
generate numbers | Excel Discussion (Misc queries) | |||
generate bates numbers on labels | Excel Worksheet Functions | |||
How can I generate avery address labels from database | New Users to Excel | |||
generate random numbers | Excel Worksheet Functions |