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

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

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