Printing labels, automatically add "END" macro

pavimeus

New Member
Joined
Nov 4, 2002
Messages
45
I work for a list management company. We print A LOT of mailing labels for our clients. The type of labels we use is perforated for easy tear, 3 across, 12 down, 36 to a page (where the perforations begin), and 15,000 to a box.

It is far more time efficient to print multiple “orders” at a time, using the entire box (15,000 labels), rather than printing one “order” at a time, per client. The issue is, how do we separate each batch of orders from 15,000 labels, that is going to X no. of different clients, once the entire print job is done. We could flip through the pages and look for order numbers, and go to work with a pair scissors.

Too much work.

So here’s what we are doing. All the labels are mail merged into a Word document, from an Excel spreadsheet. There is an order no. at the start of each “order”. We can flip through the pages and look for this order no. so that we know where to begin. On the end of the excel spreadsheet for each order, we insert a series of cells with the word “END”, until the total number of rows, per order, is divisible by 36.

For example, if we have an order calling for 1136 labels (each label constitutes one row of data on the spreadsheet), then we will add 16 additional cells with the word “END” so that the total equals 1152, which is divisible by 36. Recall that 36 is where each perforation begins. So once the print job is done, we look for the order no. (so that we know where to begin), and then a series of “END”s, and if done correctly, it will tear right at the perforation. Then we go on to the next order, so on a so forth.

It works, but a bit tedious.

After all the rambling above, here, finally, is my call for help. Could you good folks out there write a macro that automatically does the calculations and inserts the required no. “END”s, for each order? (The “END”s have to be inserted at the top of the spreadsheet). Then we can copy and paste the portion on to another spreadsheet, until we max out 15,000 rows, therefore allowing us to run several orders per box, all at once.

I hope I have explained things well enough. Thank you very, very much for your feedback. Any all thoughts welcome.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
Try the following:

Sub InsertEnds()

Dim lngRowCount As Long
Dim lngInsertNumber As Long
Dim lngInsertCounter As Long

Range("A65536").Select
Selection.End(xlUp).Select
lngRowCount = ActiveCell.Row
lngInsertNumber = 36 - lngRowCount Mod 36
If lngInsertNumber > 0 Then
For lngInsertCounter = 1 To lngInsertNumber Step 1
Range("A1").Insert shift:=xlDown
Range("A1").Formula = "END"
Next lngInsertCounter
Range("A1").Select
End If

End Sub
 

pavimeus

New Member
Joined
Nov 4, 2002
Messages
45
BRILLIANT!! Thank you Artemus.

A slight tweak is needed.

Right now the spreadsheet looks like this after the macro is run;

END First Name MI Last Name Suffix Street Address City State ZIP Code
END Order No.
END Name1 A Surname1 Jr 123 Any Street Anytown XY 12345-6789
END Name2 B Surname2 124 Any Street Anytown XY 12345-6790
END Name3 A Surname3 125 Any Street Anytown XY 12345-6791
END Name4 B Surname4 III 126 Any Street Anytown XY 12345-6792
END Name5 A Surname5 Sr 127 Any Street Anytown XY 12345-6793
Title Name6 B Surname6 128 Any Street Anytown XY 12345-6794
Name7 A Surname7 129 Any Street Anytown XY 12345-6795
Mr. Name8 B Surname8 IV 130 Any Street Anytown XY 12345-6796
Ms.
Mrs.
Dr.
Mr.
Ms.
Mrs.
Dr.



It should be

END
END
END
END
END
END
END
Title First Name MI Last Name Suffix Street Address City State ZIP Code
Order No.
Mr. Name1 A Surname1 Jr Anytown XY 12345-6789
Ms. Name2 B Surname2 Anytown XY 12345-6790
Mrs. Name3 A Surname3 Anytown XY 12345-6791
Dr. Name4 B Surname4 III Anytown XY 12345-6792
Mr. Name5 A Surname5 Sr Anytown XY 12345-6793
Ms. Name6 B Surname6 Anytown XY 12345-6794
Mrs. Name7 A Surname7 Anytown XY 12345-6795
Dr. Name8 B Surname8 IV Anytown XY 12345-6796




Perhaps

Range("A1").Insert shift:=xlDown
Range("A1").Formula = "END"

A1 should be "A:A"?

Thank you again.
 

pavimeus

New Member
Joined
Nov 4, 2002
Messages
45

ADVERTISEMENT

Artemus, I tried it (with the changes) and it doesn't work. Please advice.

Thank you.
 

pavimeus

New Member
Joined
Nov 4, 2002
Messages
45

ADVERTISEMENT

Folks, I could really use your help on this.

Thank you very much.
 

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
Try this one:

Sub InsertEnds()

Dim lngRowCount As Long
Dim lngInsertNumber As Long
Dim lngInsertCounter As Long

Range("A65536").Select
Selection.End(xlUp).Select
lngRowCount = ActiveCell.Row
lngInsertNumber = 36 - lngRowCount Mod 36
If lngInsertNumber > 0 Then
For lngInsertCounter = 1 To lngInsertNumber Step 1
Selection.EntireRow.Insert
Range("A1").EntireRow.Insert
Range("A1").Formula = "END"
Next lngInsertCounter
Range("A1").Select
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,022
Members
410,647
Latest member
bernardazar
Top