Generate numbers for labels

  • Thread starter Thread starter Neil Grantham
  • Start date Start date
N

Neil Grantham

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
 
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
(e-mail address removed)
 
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
 
Back
Top